Category: DAX

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

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.

Comments closed

Chris Webb points out another benefit of DAX window functions:

The new DAX window functions (announced here, more details on Jeffrey Wang’s blog here and here) have generated a lot of excitement already – they are extremely powerful. However one important benefit of using them has not been mentioned so far: they can give you much better performance in DirectQuery mode because they make it more likely that aggregations are used. After all, the fastest DirectQuery datasets are the ones that can use aggregations (ideally Import mode aggregations) as much as possible.

As always, Chris has a demo for us, so check it out.

Comments closed