Press "Enter" to skip to content

Category: DAX

Cumulative Values in Power BI

Matt Allington has a video for us:

The table on the left above shows the change in head count in each department, and is to be populated by the manager. But when it comes to reporting, we really need to know the total change in headcount as a number for each year, not just the first year the change occurred (as shown in the table to the right, above).

There are different ways to solve this problem, but I decided to do it using a combination of Power Query and DAX. 

Click through for the video solution.

Comments closed

Returning 0 Instead of BLANK in DAX

Marco Russo and Alberto Ferrari want to see zeroes in specific circumstances:

What makes this specific product interesting is that the product had sales in 2007, no sales in 2008 and it started selling again in 2009. Its behavior is different than the other products. Indeed, for most of these products one can argue that they start to produce sales when they were introduced in the market. Their behavior is quite intuitive: no sales up to a given point in time, then they start selling. We want to highlight this specific product because it shows a gap in sales when it was already present on the market. For other products, we are happy to blank them until their first sale. By doing this, we show gaps when they are real, and we avoid showing non-relevant information, that is products that could not produce sales because they were not even available to sell.

Read on to see how they do this.

Comments closed

Understanding SUMMARIZE in DAX

Alberto Ferrari dives into a DAX operator:

If you like to follow best practices, you can just read this paragraph out of the entire article. If you are using SUMMARIZE to calculate new columns, stop. Seriously, stop doing it. Right now. Open your existing DAX code, search for SUMMARIZE and if you find that you are using SUMMARIZE to compute new columns, add them instead by using ADDCOLUMNS.

At SQLBI we are so strong on this position that we deliberately omitted a part of the detailed description of the behavior of SUMMARIZE in our book. We understand how SUMMARIZE works but we do not want your code to return inaccurate results, just because you use a function without understanding when its result might be different from the result you expected.

Read on as Alberto explains why, as well as the details of SUMMARIZE and how easily you can find yourself in a mess with it.

Comments closed

A Review of Tabular Editor 3

Matt Allington reviews a paid product:

Tabular Editor is a Power BI Tabular Modelling productivity tool developed by Daniel Otykier. I blogged about Version 2 of the Tabular Editor in this article here. The 3rd edition of Tabular Editor has just been released, and it is a major upgrade from version 2. TE 3 is not free, but in my view, the productivity benefits make it a must have piece of software for anyone that is regularly writing DAX in Power BI Desktop.

Read on for the review.

Comments closed

Counts of Last-Known States of Items with DAX

Phil Seamark has an interesting problem:

The requirement was simple enough. Take the following dataset and, for any given day, produce a count of each possible State using the last known State for any given TestID. The dataset contains six unique Test IDs (A through F). At any given point in time, we first want to establish the last State for each TestID. We also want to group this by day and produce a count value for each possible State. Note, a given TestID can have more than one event in a day, and we only care about the last one.

I’m particularly interested in this because I find a lot of merit in the event-based structure in Phil’s input dataset, but it can be tricky going from that to data in a shape the customer likes.

Comments closed

Showing Ranges of Data with a Single Slicer

Marco Russo and Alberto Ferrari have another good use case for calculation groups:

Because the axis of the visual must show months outside of the slicer selection, we cannot use the usual Date[Calendar Year Month] column. Indeed, if we put the Date[Calendar Year Month] column on the X-axis, the only visible month would be the selected month. It is worth remembering that the selection of the values to show on the axis is independent from the measure. If a slicer is filtering one month, there is no way to show additional months from the same table on either the rows or the columns (or the X-axis, as in the example).

Therefore, we must create a separate table that is not subject to filtering from the slicer. This way, columns from that table show all the rows, and we can control their visibility through a measure. Once the new table is in place, we write a measure that produces a value for only the last six months out of all the months visible, and leaves the remaining months blank in order to hide them.

Read on to see how.

Comments closed

Rolling 12-Month Averages in DAX

Alberto Ferrari shows how to calculate a rolling 12-month average in DAX:

The measure we want to compute is Rolling Avg 12M, which computes the rolling average of the Sales Amount measure over the last 12 months. When you project the rolling average on a chart, the resulting line is much smoother; it removes the spikes and drops that would make it difficult to recognize a trend in sales.

Click through to see two ways to do this: via a DAX measure and then as a calculation group.

Comments closed

DAX and Case Sensitivity

Marco Russo and Alberto Ferrari talk about case sensitivity:

Every new language defines its own rules of case-sensitivity. R and Python are case-sensitive, DAX is not. It is not that one is right and the others are not; it is really a matter of personal taste of the author of the language. We would say that there is an equal number of pros and cons in both choices. Therefore, there is no definitive choice. That said, a choice needs to be made on two aspects: the language itself and the way it considers strings. Pascal, for example, is case-insensitive as a language, but string comparison is case-sensitive. The M language, in Power Query, is case-sensitive despite living in the same environment as DAX. DAX is case-insensitive as a formula language. 

Maybe it’s because I like living in the SQL world so much, but I highly prefer case-insensitivity as the default and case-sensitivity only when necessary.

Comments closed


Marco Russo and Alberto Ferrari takes us through the DEFINE TABLE statement in DAX:

Introduced in December 2020, the DEFINE TABLE statement lets you define a calculated table local to a query. The table is not persisted in the model, it exists only for the lifetime of the query. Apart from that, it is a calculated table in every sense of the term albeit with some limitations.

The extension of DAX with the capability to define calculated tables local to a query is needed in order to support composite models (DirectQuery for Power BI datasets and Azure Analysis Services). There are no limitations on the use of the feature, so you can take advantage of local tables in any DAX query. We refer to calculated tables defined in a query as query calculated tables, or query tables for short.

Click through for an example of how it works.

Comments closed