DAX: The "Python" of the BI world

Before I fell in love with Tableau's versatile drag and drop visualization functions, I was head over heels with Python, and it's libraries like Pandas, Seaborn and Plotly. I enjoyed coding and this is something I rarely do anyomore when working with Tableau and Alteryx.

When we started working with PowerBI, I found the graphical user interface to be cluttered, chaotic and overwhelming. But then I learned about DAX. Once I discovered that DAX is more than writing simple one-liners - that you can create tables, and even assign variables, similarly to my beloved Python - I was hooked!

Three really cool things that DAX can do

Assigning Variables

In most BI tools, you're limited to "Scalar" values (single numbers). In DAX, you have Variables and Return statements. For a Python user, this feels like home.

You declare a variable with putting VAR before the statement, and Return the Variable using a RETURN statement like in this example:

Profitability = 

VAR Profit = Movies[Box Office] - Movies[Budget]


VAR Category = SWITCH(
    TRUE(),
    Profit < 0, "Loser",
    Profit < 5e8, "Low",
    Profit < 1e9, "Medium",
    "High"
)

RETURN Category

The advantage is not only less typing. When you store a calculation in a variable, Power BI calculates it once and remembers it. Without variables, if you use a calculation four times in a formula, DAX might try to calculate it four times.

Note: Unlike Python, you cannot change the value of a variable, once you declare it. You need to declare a new variable for this. Also: A DAX variable defined with VAR only exists within the specific Measure or Calculated Column where it was written.

Creating a subset of your table

The CALCULATETABLE function in DAX gives you the possibility to filter your data table and creates a new virtual table. This reminds me of working with Pandas DataFrames. In Python, you would do something like this:

high_sales_de = df[(df['Category'] == 'Electronics') & (df['Country'] == 'Germany')]

in DAX, this would look something like this:

VAR HighSalesDE = 
    CALCULATETABLE(
        'Sales',
        'Sales'[Category] = "Electronics",
        'Sales'[Country] = "Germany"
    )

Iterators: The "for loops" of DAX

While the standard calculations, like SUM or AVERAGE calculate values based on entire columns, iterator functions like SUMX and AVERAGEX calculate values based on rows. DAX loops through a table in memory, similar to a for loop in Python.

Row-based calculations are also possible in Tableau and Alteryx, but less straight-forward. In Alteryx, if you want to calculate Price * Quantity for every row and then sum it up, you usually create a new column. In Tableau, you would usually create a new calculated field. In both cases you double the data. In DAX, SUMX allows you to do these row-based calculations directly without ever creating a new field!

Conclusion

If you like to spice up the drag and drop with a bit of coding, DAX is just what you need. With DAX you can do so much more than just simple IF statements. By the way, have you already tried the SWITCH function? It’s the clean, more readable version of IF statements that DAX has to offer.