Category: DAX

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.

Koen Verbeeck is looking to optimize code which uses CALCULATE and FILTER together:

There have already been many posts/articles/books written about the subject of how CALCULATE and FILTER works, so I’m not going to repeat all that information here. Noteworthy resources (by “the Italians” of course):

• Filter Arguments in CALCULATE
• How CALCULATE works in DAX
• From SQL to DAX: Filtering Data

In this blog post I’d rather discuss a performance issue I had to tackle at a client. There were quite a lot of measures of the following format:

Click through for a couple iterations of this.

Meagan Longoria has lost customers and is trying to find them:

I was testing some calculations in my tabular model when I noticed that my Lost Customers calculation wasn’t working as expected. This was rather interesting to me since the calculation I was using was from DAXPatterns.com.  After some experimentation, I determined that the difference between my data situation, and the situation that fit the DAX Pattern was that my customer dimension was a Type 2 Slowly Changing Dimension. That meant I couldn’t use the customer key to identify a unique customer, since each customer could have multiple customer keys  (surrogate keys) with the same customer number (business key). Specifically, if a customer made a purchase in December 2015, then changed their name, then made a purchase in January and February, my calculation was counting them as lost because it was not recognizing that the customer with the new name and same customer number was actually the same customer.

She ends up with two solutions, each with different trade-offs.  Knowing as little DAX as I do, looking at two different ways of solving this problem is great because it gives you more insight into language semantics.

Matt Allington shows how that reducing cardinality helps with reducing data sizes with DAX:

With both of these concepts combined, the file size was reduced from the original 264 MB to 238 MB, a reduction of almost 10%.  You can see where the space savings have come from by comparing the before and after column sizes in the 2 tables below.  The SalesValueExTax column (65MB) was replaced with the Margin column (44MB) and the CostValue column (63MB) was replaced with the CostPerCase column (50MB).

Check it out, as well as the memory tool.