Press "Enter" to skip to content

Category: DAX

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

DEFINE TABLE in DAX Queries

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

Debugging DAX via Tooltips

Matt Allington has a workaround for us:

DAX is a tricky language; on the surface it is deceptively simple, but under the hood it can quickly become complex and it can take many years to master. If you have ever typed a formula and crossed your fingers when you press Enter, then you know what I mean. If you are reading this article, you are no doubt already on your own DAX learning journey.

As you become more competent at DAX, you will start to write more complex formulas that behave differently depending on the filters in your visuals.  One such example is the P&L report that I shared in 2020.  This report contains quite a tricky formula. It was built slowly and methodically, one step at a time, until it was working as desired.  This is the best way to write DAX.  If you want to see how I do it, then go back to the article linked above and watch the video.  While I recommend this as the best approach to writing formulas, the reality is that sometimes you will need to go back to a formula you wrote previously and debug that formula.  Maybe it is not working correctly, or maybe you need to enhance it for some reason.  Whatever the reason, today I am sharing with you my technique to debug complex formulas using tool tips.

Read the whole thing.

Comments closed

Using DEFINE COLUMN in DAX Queries

Marco Russo and Alberto Ferrari show off some new DAX syntax:

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

The extension of DAX with the capability to define calculated columns local to a query is needed in order to support composite models over Analysis Services (AS). There are no limitations in the use of the feature. For this reason, you can take advantage of local columns in any DAX query. We refer to calculated columns defined in a query as query calculated columns, or query columns for short.

Click through to see it in action. I like this idea a lot, though do read their note regarding performance, contrasting it with ADDCOLUMNS.

Comments closed

Top N with Others in Power BI

Marco Russo and Alberto Ferrari cover a pain point in Power BI:

The VisibleProducts variable contains a list of products for the selection currently displayed in the visual. In the example, we have the top 3 products for each Product Category included in our report. The ranking that is returned is only up to the value selected in the TopN parameter – for this reason, we can use the result of Ranking by Sales to filter the visual, including only the products ranked in the 1-to-TopN Value range. We use a filter in the Power BI filter pane to accomplish this task.

This is a common enough pattern that I do wish Power BI made it easy.

Comments closed