First-timing DAX
Tags: powerbi, dax, beginners
Published: 2021-12-26 10:20:08

A few weeks ago, I wrote [a blog about my first time encounter with Power Query](https://dev.to/marvintensuan/power-bis-power-query-m-for-python-programmers-1l6g).

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`.

```fsharp
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](https://www.reddit.com/r/PowerBI/comments/r8qyuz/power_bis_power_query_m_for_python_programmers/) 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)`

```js
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](https://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:

```js
/* 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:

```js
AnotherTableFromEvalute = 
SUMMARIZECOLUMNS(
    Stonks[Price],
    KEEPFILTERS( TREATAS( {"2021-12-23"}, Stonks[Date] )),
    KEEPFILTERS( TREATAS( {"AAPL"}, Stonks[Ticker] ))
)
```

Or like this:

```js
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*.

```js
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](https://community.powerbi.com/t5/Desktop/Embedding-up-and-down-arrows-in-visual-and-changing-them-based/m-p/327225) 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.
- ~~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

- [Power Query (M) for Python Programmers | marvintensuan @ DEV.TO](https://dev.to/marvintensuan/power-bis-power-query-m-for-python-programmers-1l6g)
- [My Reddit Post for Power Query]([Redditor](https://www.reddit.com/r/PowerBI/comments/r8qyuz/power_bis_power_query_m_for_python_programmers/))
- [dax.guide](https://dax.guide/)
- [Arrow Solution](https://community.powerbi.com/t5/Desktop/Embedding-up-and-down-arrows-in-visual-and-changing-them-based/m-p/327225)
- [Unicode](https://www.fileformat.info/info/unicode/block/geometric_shapes/list.htm)