Category: DAX

Phil Seamark does some manufacturing analysis:

I always enjoy it when we get new DAX functions, especially so for the new set of WINDOW Functions recently added. As part of the April 2023 release of Power BI Desktop, we now have a RANK function and the ability to use a measure to control the order within the existing WINDOW function.

The first thing that sprung to my mind was to see how a Pareto calculation might leverage the new capability.

The basic idea of a Pareto calculation is to create a curve like representation of data ordered from largest to smallest.

Read on to see how.

Chris Webb has an update for us:

Some good news for those of you using DirectQuery mode in Power BI: the ApproximateDistinctCount DAX function, which returns an estimate of the number of the distinct values in a column and which can be a lot faster than a true distinct count as returned by the DistinctCount function, is now available to use with BigQuery, Databricks and Snowflake sources. It only worked with Azure SQL DB and Synapse before; RedShift is coming soon. You can use it in exactly the same way that you would with the DistinctCount function except that it only works in DirectQuery mode.

As always, there’s an example. I do wonder if the DAX function uses the same HyperLogLog algorithm that SQL Server uses for its approximate count distinct.

Marco Russo and Alberto Ferrari cover the blank row:

We dedicated a previous article to the blank row in DAX. In that article, the goal was to explain the differences between VALUES and DISTINCT. This article here focuses on how important it is to generate the blank row to guarantee that totals are always correct.

The blank row is created for regular relationships that are invalid – that is, when there is at least one row on the many-side that does not have a matching row on the one-side of the regular relationship. The same does not happen for limited relationships, which do not generate a blank row in similar conditions. Therefore, if a model contains a limited invalid relationship, developers must pay extra attention to how they create reports to avoid obtaining inaccurate results.

Read on for an example of what they mean.

Marco Russo and Alberto Ferrari lay out the foundations of window functions:

Window functions by themselves do not increase the expressivity of DAX. Most if not all of the calculations performed with window functions can be expressed with more complex DAX code. The goal is to simplify authoring these calculations and improve their performance.

These new functions also introduce a new concept to the DAX language: “apply semantics”. We will publish more articles about window functions and “apply semantics” over time. SQLBI+ subscribers will get a dedicated video course later this year and already have access to the window functions whitepaper we are currently writing.

Click through for an introduction.

Leo Tachev isn’t down with the copy-pasta:

Suppose you need to calculate a percentage of grand total measure. Easy, you can use the Power BI “Show value as” without any DAX, right? Now suppose that you have 50 Table visuals and each of them require the same measure to be shown as a percentage of total. Although it requires far more clicks, “Show value as” is still not so bad for avoiding the DAX rabbit hole. But what about if you need this calculation in another measure, such as to implement a weighted average? Now, you can’t reference the Microsoft-generated field because it’s not implemented as a measure.

Click through for one solution.

Ed Hansberry wants us to slow down and think:

First off I want to make sure it is clear that I am not saying “never use calculated columns.” I am not. What I am saying is they should be avoided, and there are several reasons. I am going to focus on the model size, and thus performance. Note that the data I am going to show is a contrived set of data for demonstration purposes only to explain.

Read the whole thing, including a set of questions you might want to ask yourself before deploying that PBIX file with calculated columns in it.

Allison Kennedy has a quick keyboard tip for us:

One of my favorite keyboard shortcuts in DAX Power BI desktop is:

Ctrl Shift L

This lets you easily change multiple values in one go. 

Click through to see how it works, including a short video.

Marco Russo and Alberto Ferrari bust out the oscilloscope:

Finding errors in DAX measures has always been difficult because a single DAX measure produces different results in different cells of the same report, and we do not have a direct way of executing the code step by step in Power BI. This article describes four techniques to find an error in a DAX formula: we start with variable manipulation that does not require any external tool, and then we see how to leverage features available in DAX Studio, DAX Debug Output, and Tabular Editor 3.

Do check this out, especially if you spend a lot of time writing DAX code.