TheLazy.Dev
Portfolio GitHub

Blog

powerbi |dax |beginners

First-timing DAX

marvintensuan

Marvin

Posted on December 26, 2021

First-timing DAX

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]
})
Enter fullscreen mode Exit fullscreen mode

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]
Enter fullscreen mode Exit fullscreen mode

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"
    )
)
Enter fullscreen mode Exit fullscreen mode

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 */
Enter fullscreen mode Exit fullscreen mode

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] ))
)
Enter fullscreen mode Exit fullscreen mode

Or like this:

MyTableFromAssignedVar = 
VAR
 ThisIsTheVariable =
    SUMMARIZECOLUMNS(
        Stonks[Price],
        KEEPFILTERS( TREATAS( {"2021-12-23"}, Stonks[Date] )),
        KEEPFILTERS( TREATAS( {"AAPL"}, Stonks[Ticker] ))
    )
RETURN
    ThisIsTheVariable
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
 )
Enter fullscreen mode Exit fullscreen mode

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.
  • Google Search 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 EVALUATE and 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.

Links

💖 💪 🙅 🚩
marvintensuan
Marvin

Posted on December 26, 2021

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

First-timing DAX
powerbi First-timing DAX

December 26, 2021