A few weeks ago, I wrote a blog about my first time encounter with Power Query.
In this article, I will outline my blind experience with DAX as a first-time user. We will be solving a simple problem and walk through how I got to the solution.
Is Power BI user-friendly? Is DAX beginner-friendly? How far can you go without reading the docs? Continue reading to find out
Case Study
Input
We will be using the following data:
| Date | Ticker | Price |
|---|---|---|
| 2021-12-23 | AAPL | 275.0 |
| 2021-12-23 | GOOG | 2940 |
| 2021-12-24 | AAPL | 276.5 |
| 2021-12-24 | GOOG | 2941 |
You may enter below code on Power Query to load it in Power BI. I will name the table Stonks.
Table.FromRecords({
[Date="2021-12-23", Ticker="AAPL", Price=275.0],
[Date="2021-12-23", Ticker="GOOG", Price=2940],
[Date="2021-12-24", Ticker="AAPL", Price=276.50],
[Date="2021-12-24", Ticker="GOOG", Price=2941]
})
Output
Create a new measure that calculates the day-on-day change. For bonus points, we would like to display an indicator on whether that change is positive (▲) or negative (▼).
I'll be using both Text Box and Table do display these measures on a Power BI page.
Step Zero: What I know
In my previous blog, I wrote a one-liner code depicting DAX.
Revenue = [Sales] * [PricePerUnit]
I mentioned that
This operation should be fairly familiar to Excel users.
This is what I mean by that.
| A | B | C | |
|---|---|---|---|
| 1 | Sales | PricePerUnit | Revenue |
| 2 | 100,000 | 2 | =A2*B2 |
| 3 | 50,000 | 2.1 | =A3*B3 |
I also mentioned that
There's definitely going to be an overlap between what Power Query and DAX are capable of. Of course, one feature is going to be easier in the other and vice versa. But ultimately, it will all boil down to preference.
One Redditor pointed out that actually, one does not want to use Power Query for running analytics. I agree; even though I haven't used DAX, I have already gotten a feel on how Power Query handles a large amount of data.
I have also skimmed DAX Overview Official Docs. I saw something about DAX Studio so I downloaded it.
Breaking the problem into smaller parts
Our problem statement is: calculate the day-on-day change of AAPL's price.
To do that we need to:
- get AAPL's current price (276.50)
- get AAPL's previous price (275.0)
- subtract the previous price from the current price
Converting these steps into some technical tasks will simplify it even further.
- get a value from the table
- perform arithmetic operation
But then how do we get 275 from the table?
Should we Filter the table so that only AAPL will remain?
Problem Solving
Feel free to skip this section if you want to go straight ahead to the solution.
The UI
First things first: the User Interface (UI). Creating a new table based on Stonks and filtering it does NOT work.
Right-clicking 275 and selecting New Measure... also does NOT work.
Filter
If only we can filter Stonks[Date] and Stonks[Ticker], and then maybe we can get the value from Stonks[Price]
ALAS! There's a FILTER function.
The FILTER function which goes like this: FILTER(Table, FilterExpression)
MyNewTable =
FILTER(
Stonks,
AND(
Stonks[Ticker]=="AAPL",
Stonks[Date]=="2021-12-23"
)
)
The result is the first row of Stonks
| Date | Ticker | Price |
|---|---|---|
| 2021-12-23 | AAPL | 275.0 |
This is nice. Now, all we have to do is to reference the MyNewTable[Price], right? Right???
DAX Studio
DAX Studio is cool and intuitive. It features a Query Builder which enables users to drag-and-drop fields from the Power BI dataset and do the Query Building for the user. DAX Studio also links you to a site called dax.guide.
The Query Builder is very similar to Excel's Pivot Table Field List.
I dragged Price to the Columns/Measures pane and on Filters, Date is 2021-12-23 and Ticker is AAPL.
The Run Query button returned the following table:
| Price |
|---|
| 275 |
and the Edit Query button returned the following query:
/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
Stonks[Price],
KEEPFILTERS( TREATAS( {"2021-12-23"}, Stonks[Date] )),
KEEPFILTERS( TREATAS( {"AAPL"}, Stonks[Ticker] ))
)
ORDER BY
Stonks[Price] ASC
/* END QUERY BUILDER */
Knowing SQL, I know it's safe to remove the ORDER BY "clause".
My Solution
My problem with FILTER and SUMMARIZECOLUMNS is that they create new tables, which clutters up my namespaces. The reason I specified that I need a "new measure" is because I want my values to be associated with the Stonks table (and rightfully so).
FILTER and SUMMARIZECOLUMNS gives us tables, hence why those cannot be used for measures, or at least blanketly. For some reason, I cannot reference the price in the text box when using the FILTER solution.
The query as returned by the Query Builder also cannot be completely used right off-the-bat (Power BI throws errors). It has something to do with EVALUATE. I've yet to fully understand it but the way it works is that the above query can be used as follows:
AnotherTableFromEvalute =
SUMMARIZECOLUMNS(
Stonks[Price],
KEEPFILTERS( TREATAS( {"2021-12-23"}, Stonks[Date] )),
KEEPFILTERS( TREATAS( {"AAPL"}, Stonks[Ticker] ))
)
Or like this:
MyTableFromAssignedVar =
VAR
ThisIsTheVariable =
SUMMARIZECOLUMNS(
Stonks[Price],
KEEPFILTERS( TREATAS( {"2021-12-23"}, Stonks[Date] )),
KEEPFILTERS( TREATAS( {"AAPL"}, Stonks[Ticker] ))
)
RETURN
ThisIsTheVariable
After a few reading through some docs, I thought that instead of reducing my original table to smaller parts, maybe I could just crawl through the values of my original table, like a VLOOKUP! Fortunately enough, there's a LOOKUPVALUE function.
LOOKUPVALUE returns a value, so it could be used as a basis of a measure.
AAPL_DoD =
VAR
prev =
LOOKUPVALUE(
Stonks[Price],
Stonks[Date], "2021-12-23",
Stonks[Ticker], "AAPL"
)
VAR
curr =
LOOKUPVALUE(
Stonks[Price],
Stonks[Date], "2021-12-24",
Stonks[Ticker], "AAPL"
)
RETURN
curr - prev
This solution could still use some refactoring to make it more generalizable but that would be a topic for another day.
BONUS: arrow indicators. I saw this thread solving this exact problem, actually.
AAPL_DoD_arrow =
IF(
[AAPL_DoD]>0,
UNICHAR(9650),
UNICHAR(9660)
)
Closing Thoughts
It's a holiday so I'll write these in a list.
- You don't have to be a walking documentation. Knowing where to read and when, that is a skill.
-
GoogleSearch engines are your friend. - Still... expericence > Official docs and dax.guide > search. As with any other tools, building your experience would be the best way to go forward.
- The official docs made a point that to use DAX queries, it's advisable to first have a solid foundation on the basics. I kind of agree but as a big picture guy, I'm glad to see things like
EVALUATEand how different things can be between Query Builder and Power BI itself. - I used to think that DAX is just Excel++. Now, I think DAX requires you to adopt its own mental model. Instead of DAX tapping on Excel as its base, it's actually more like DAX and Excel shared a common ancestor and now, it is in a league of its own.
Top comments (0)