Category: DAX

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.

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.

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.

Marco Russo shows us a way of improving performance on conditional statements:

Consider the following measure.

1 2 3 4 5

Margin := IF (     [Sales Amount] > 0 && [Total Cost] > 0,     [Sales Amount] - [Total Cost] )

The basic idea is that the difference between Sales Amount and Total Cost should be evaluated only whether both measures are greater than zero. In such a condition, the DAX engine produces a query plan that evaluates each measure twice. This is visible in the storage engine requests generated for the following query.

Read on to see how Marco avoids this performance issue.

Matt Allington explains what the TREATAS function does:

The TREATAS function can be used to detect filters from your visual (filter context) and then apply these filters to a disconnected table in your data model.

• It takes a source table (first parameter) and applies the values from that table to columns in a target table (second and subsequent parameters).

• You can use a function like VALUES as the first parameter to detect the initial filter context in a visual and hence TREATAS can propagate filter context to the target table.

• You do not need to have a physical relationship between the source table and the target table.  It therefore means that TREATAS can be used as a virtual many to many relationship.

• You can pass multiple filters (columns) from the source table to the target table.  TREATAS can therefore can be used to apply multiple relationships (ie on more than one column) between tables.

Read on for a good example of how this works.