Press "Enter" to skip to content

Category: DAX

Common DAX Error Messages

Published 2019-01-22 by Kevin Feasel

Marco Russo takes us through some of the more common Power BI error messages around writing DAX:

The message should help the author fix the code, but sometimes the text suggests a possible action without describing the underlying issue. The goal of this article is to explain the more common DAX error messages by providing a more detailed explanation and by including links to additional material. If some terms are not clear, look at the linked articles or consider some free self-paced training such as Introducing DAX.

Click through for several examples.

Comments closed

Name Conflicts In DAX

Published 2019-01-17 by Kevin Feasel

Marco Russo takes us through an issue with naming in DAX:

You deploy this model and start creating reports using the Sales Returning Customers measure. So far, so good. One day, you need to extend the data model importing a new table that you decide to name ReturningCustomers. As soon as you import the new table named ReturningCustomers, your measure Sales Returning Customers stops working. The reason is that the ReturningCustomers variable generates a name conflict with the table that has the same name, as you can see from the error message.

‘ReturningCustomers’ is a table name and cannot be used to define a variable.

Marco has some advice if you’re in a situation where you are liable to see this pop up.

Comments closed

Displaying Human-Readable Month Sets With DAX

Published 2018-12-28 by Kevin Feasel

Alberto Ferrari wants to show sets of contiguous months using DAX:

Today I woke up with an interesting question, about how to show a selection of months in a nice way, detecting contiguous selection. You can easily understand the desired solution from the following figure:

I enjoyed writing a quick solution, which is worth sharing. The code is somewhat verbose, but this is mainly for educational purposes (meaning I did not want to spend time optimizing it). I will likely write a full article on it, for now, just enjoy some DAX code:

I removed the image, but to get the gist (and get you to click through to see it in its beauty), it reads “January, March-April, August-December”

Click through for Alberto’s quick-and-dirty solution and then Chris Webb’s improvement.

Comments closed

Showing Forecasts With Actuals In Power BI

Published 2018-11-06 by Kevin Feasel

Alberto Ferrari shows us how we can incorporate actuals and forecasted values in the same Power BI visuals:

The Forecast measure in the demo model is quite an advanced piece of DAX code that would require a full article by itself. The curious reader will find more information on how to reallocate budget at different granularities in the video Budgeting with Power BI. In this article, we use the Forecast measure without detailed explanations; our goal is to explain how to compute the next measure: Remaining Forecast.

The Remaining Forecast measure must analyze the Sales table, finding the last day for which there are sales, and only then computing the forecasts.

Read the whole thing.

Comments closed

Optimizing DAX SWITCH Statements With Variables

Published 2018-10-26 by Kevin Feasel

Marco Russo gives us some advice on optimizing IF and SWITCH statements in DAX:

Though the DAX engine might reuse the result obtained for the same measures in the same filter context (Sales Amount and Sales LY), this is not always the case. In this scenario, variables are a good way to ensure a better optimized code execution.

However, variables should only be used within their respective scope. For example, if a variable is defined before a conditional statement, then the variable will be evaluated regardless of the condition. This has a strong performance impact in case there are disconnected slicers in the report. To elaborate on this, consider the following report where a Time Selection table is used to define a slicer that controls which columns of the matrix should be visible. The matrix contains a single measure called Sales, whose content depends on the period selected in the column.

Read on for more.

Comments closed

Word Counts In DAX

Published 2018-10-15 by Kevin Feasel

Philip Seamark shows us a way of splitting strings into words in DAX:

Here is a technique you might consider if you need to split text down to individual words.  This could be used to help count, rank or otherwise aggregate the words in some longer text.  The approach detailed here uses spaces as a delimiter and will not be tripped up if multiple spaces are used between words.

There is no SPLIT function in DAX, so this approach uses the MID function to help find words.

The PBIX file used for the blog can be downloaded here.

[Updated 14th Oct, 2018]
A slightly updated version that uses UNICHAR/UNICODE to preserve the case (“A” versus “a”) of each letter can be downloaded here. The reason for this is DAX stores a dictionary of unique values for every column.  It is the first instance of any value that is added to the dictionary and assigned a new ID.  Subsequent values that are considered the same “A” and “a” are considered the same are assigned the same ID.  Using the UNICHAR/UNICODE version helps preserve the original case of each letter.

It’s an interesting approach and reminded me a bit of using a tally table to split strings in T-SQL.

Comments closed

Getting A Specific Rank In DAX

Published 2018-10-09 by Kevin Feasel

Marco Russo shows us how to get the Nth element in a list using DAX:

The complexity of the calculation is in the Nth-Product Name Single and Nth-Product Sales Amount Single measures. These two measures are identical. The only difference is the RETURN statement in the last line, which chooses the return value between the NthProduct and NthAmount variables.

Unfortunately, DAX does not offer a universal way to share the code generating tables between different measures. Analysis Services Tabular provides access to DETAILROWS as a workaround, but this feature cannot be defined in a Power BI or Power Pivot data model as of now.

Indeed, the code of the two measures is nearly identical.

Read on for code and explanation.

Comments closed

The DAX Guide

Published 2018-09-21 by Kevin Feasel

Marco Russo announces a new site:

  • What is DAX Guide? DAX Guide is a website offering a complete reference to the DAX language. Every function is presented with its complete syntax, a short description, and links to related functions and articles.

  • Is DAX Guide a tutorial to learn DAX?No, DAX Guide is not designed as a learning tool. The goal of DAX Guide is to provide a quick reference with accurate information. The only commitment is “quality first”.

  • What are some unique features of DAX Guide?DAX Guide is updated automatically through the monitoring of new versions of Microsoft products. Every DAX function comes with a compatibility matrix describing in which Microsoft products and versions the function may be available. Additional attributes highlight which functions perform a context transition, which arguments are executed within a row context, and which functions are obsolete or deprecated – in our opinion.

If that sounds interesting to you, check it out.

Comments closed

Quartiles In DAX

Published 2018-09-07 by Kevin Feasel

Dustin Ryan shows us how to calculate quartiles using DAX:

To calculate the quartile, we’re going to use the PERCENTILEX.INC DAX function. The PERCENTILEX.INC function returns the number at the specified percentile. So for example, if I had numbers 0 and 100 in my data set, the 25th percentile value would be 25. The 50th percentile value would be 50 and the 75th percentile value would be 75, and you can figure out what the 100th percentile value would be.

Dustin shares an example with his NFL data set and also walks us through a couple of tricky situations.

Comments closed

The Basics Of DAX

Published 2018-08-22 by Kevin Feasel

Matthew Brice walks us through filters and calculations in DAX:

CALCULATE is somewhat unique in that it evaluates the 2nd, 3rd, …nth parameter first, and evaluates the first parameter last using values from my Filter Context Box. I think it is extremely helpful to list briefly the steps CALCULATE performs whenever it is invoked. (So maybe we are not at 10,000 feet, but 5,000?)

The CALCULATE function performs the following operations:

  1. Create a new filter context by cloning the existing one. (***Important visual step!***)

  2. Move rows in the row context to the new clone filter context box one by one replacing filters if it references the same column. (We will ignore this step for this post)

  3. Evaluate each filter argument to CALCULATE in the old filter context and then add column filters to the new clone filter context box one by one, replacing column filters if it references the same column.

  4. Evaluate the first argument in the newly constructed filter context.

  5. Destroy this newly created, cloned filter context box before moving on to calculating the next “cell.”

If you’re interested in getting started with DAX, this is a good place to begin.

Comments closed