Press "Enter" to skip to content

Category: DAX

Joining Tables without Relationships in DAX

Published 2024-01-02 by Kevin Feasel

Marco Russo and Alberto Ferrari have ended a relationship:

In a previous article, we saw several examples of using the NATURALLEFTOUTERJOIN and NATURALINNERJOIN functions in DAX. In that article, we saw how to join tables in the data model using existing relationships. However, if we want to join tables in the model without using the relationships in the model, we must eliminate the data lineage of the columns to use in the join condition. This article describes how to achieve this.

I suppose my question here is, when would you want to do this? I’m sure there’s a good reason to, but I’m just as sure that I don’t know it.

Comments closed

DAX in Review: 2023

Published 2023-12-29 by Kevin Feasel

Marco Russo gives us a review:

Despite the large number driven by the new INFO functions, we did not see big changes in DAX, but rather a consolidation of the new window functions.

After three years, in 2023 the composite models reached the general availability! More important, the official name is now “composite models”, dropping the previous “DirectQuery for Power BI datasets and Analysis Services” name used in preview. Add to this that “datasets” have been renamed to “semantic models” (a change we fully support at SQLBI), and it seems this year we made peace with the Microsoft naming departments (there should be more than one).

Click through for more on DAX, as well as what Marco and Alberto have been up to this year.

Comments closed

Join Functions in DAX

Published 2023-12-19 by Kevin Feasel

Marco Russo and Alberto Ferrari join datasets together:

Readers with knowledge of SQL know that the join operation is widespread in SQL queries, as it is the standard way to combine data stored in different tables. It is however uncommon to explicitly join tables in DAX because the relationships in the data model provide enough information to allow many DAX functions to work without an explicit join operation. Most of the time, the join between tables is implicit and automatic.

However, DAX has two explicit join functions: NATURALLEFTOUTERJOIN and NATURALINNERJOIN. Apparently, these functions correspond to the behavior of LEFT OUTER JOIN and INNER JOIN in SQL. However, they differ from SQL in how you specify the join condition. This article shows how these functions can be used in DAX with practical examples. If you need a more introductory article about the syntax of these functions, read From SQL to DAX: Joining Tables, where we compare the SQL syntax with similar DAX functions.

Click through to learn more about how these functions work and what their limitations are.

Comments closed

Building a Radar Chart in Power BI with SVG

Published 2023-12-06 by Kevin Feasel

Stephanie Bruno doesn’t need a built-in radar chart visual:

Radar (or spider) charts are a way to look at multiple metrics, perhaps with a different range of values for each metric, on a single chart. In this example, we’ll look at characteristics of Taylor Swift songs from a Spotify dataset (I have a daughter who still hasn’t forgiven me for not getting tickets to the Eras tour, so hopefully this will make up for it). A matrix with the radar SVG allows us to quickly compare these song characteristics (you can get the dataset and the descriptions of the characteristics here). There are existing radar/spider custom visual charts that are great, but none of them currently have a small multiple option, so we can’t use them to create the visual below, for example.

Click through to see the full example.

Comments closed

Computing Accurate Percentages in Power BI with Row-Level Security

Published 2023-11-22 by Kevin Feasel

Marco Russo and Alberto Ferrari don’t want to let any information slip out:

Let us start with a simple challenge: we want to show the percentage of sales in Europe, compared to the sales made to all customers worldwide. It is a relatively trivial question, the kind of DAX code you learn at the beginning of your Power BI career, and it can be solved with a simple measure:

But what happens when the model includes security roles? Well, that’s what you’ll have to read on to learn.

Comments closed

A Pattern for DAX Time Intelligence Functions

Published 2023-11-20 by Kevin Feasel

Allison Kennedy checks the calendar:

Time Intelligence functions in DAX change the filter context on the Date table of your model. 

Step 0: Prepare 

Before using Time Intelligence functions, you should:

Read on for a four-step process covering how to apply a time intelligence function like DATEADD() or DATESYTD() in DAX.

Comments closed

DAX Time Intelligence with a Fiscal Year Differing from Calendar Year

Published 2023-11-10 by Kevin Feasel

Olivier Van Steenlandt covers a common case:

Many companies don’t follow the regular Calendar as we know (January 1st – December 31st). They follow their own Financial Calendar (often called Fiscal Calendar) which can start at any time of the year.

Because of this, writing Year-To-Date calculations in DAX for your Tabular Model might seem challenging.

In the step-by-step example, we are working for a company that starts its Financial Year on July 1st.

Read on to see one way to do it. It doesn’t quite solve the problem Olivier brought up, but I’d also make note that having a calendar table with fiscal + calendar year information in it helps remarkably well. It can even handle multiple fiscal year concepts; as an example, a state agency I worked for had a fiscal year on July 1 but the US federal government’s fiscal year begins October 1, so it was just a matter of having StateFiscalYear and FederalFiscalYear columns.

Also, check out Olivier’s new theming, under the Data Cuisine motif.

1 Comment

Formatting DAX Expressions with Python

Published 2023-11-01 by Kevin Feasel

Sandeep Pawar makes the code a bit more readable:

There is an old Italian saying “If it’s not formatted, it is not DAX”

When you get the list of measures from SemPy, it’s not formatted and is hard to read and understand. Thankfully, the SQLBI team has made the DAX parser and the formatter available via an API. I wrote a quick function to return the formatted DAX expression of a measure. You can either pass a DAX expression or the FabricDataFrame returned by fabric.list_measures()

Click through for the process, including the Python code to do the work.

Comments closed

Minimizing Callback Counts in SUMX()

Published 2023-10-24 by Kevin Feasel

Marco Russo and Alberto Ferrari speed things up a bit:

Pushing calculations down to the VertiPaq storage engine is always a good practice. Sometimes this is not feasible. However, carefully analyzing the aggregated expression can lead to optimization ideas that produce excellent query plans.

DAX developers should not be scared of iterators. Their performance is great as long as the expression computed during the iteration can be pushed down to the VertiPaq storage engine. 

Read on to understand what they mean by callback and the enormous performance cost you’ll want to avoid.

Comments closed

Conditional Formatting in Power BI with Field Parameters and Calculation Groups

Published 2023-10-10 by Kevin Feasel

Marco Russo and Alberto Ferrari perform some formatting:

If you want to build a report where the user can choose what measure to show, you have two features available in Power BI: field parameters and calculation groups. There are pros and cons to either technique – however, we are not about to talk about those. We narrow our scenario down to a specific requirement: we want to change the color of the value depending on the measure selected.

For example, suppose we let users choose between Sales Amount, Margin, or Total Cost. In that case, we might provide visual feedback about the measure selected through different colors: black for Sales Amount, green for Margin, and red for Total Cost.

Click through for that example, though I will say that the color choices are hard to differentiate if you have protanopia and even more difficult if you have deuteranopia, so about 2% of the male population would struggle with interpreting this measure. People with protanomaly and deuteranomaly (about 6% of men) wouldn’t have too much difficulty with this particular color pairing.

Comments closed