Consider the following measure.
[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.
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.
This post shows how you can generate optimized multi-value DAX parameters in SSRS and achieve greater performance compared to the DAX PathContains function. This will be a short post that provides the SSRS expression to convert multiple SSRS parameters into a double-pipe delimited string for use in a DAX query. In other words, the goal is to use the DAX OR operator (||) instead of the PathContains function. I’m assuming the reader has experience with SSRS, so not all steps will be shown.
Read on for the example, which ended up being a 16X performance improvement.
Variables in DAX is a relatively new feature and is available in
- Power BI Desktop
- Excel 2016
- SSAS Tabular 2016
Variables are not available in Excel 2013 or Excel 2010.
Click through to see how to assign and use variables. It’s interesting to see how they’re local to a measure, so at this point at least, you can’t share variables between measures. Given what DAX is supposed to be, that’s probably the right choice.
The Check Formula button is an easily overlooked feature. However, before I hit ok and save my DAX Measure I ALWAYS press this button first! But what exactly does this button do? Well it’s checking your DAX syntax and making sure everything is written correctly. Now you COULD simply hit OK after writing your DAX and see if it errors, this is true. However when doing that your data model is actually attempting to calculate the DAX measure in the background as well. Not a big deal with a few thousand rows, but if you’re working with a model that has millions of rows then that could take a long time for it to calculate, and then error!
The smart thing to do is to check your DAX syntax using the Check Formula button BEFORE hitting ok. Checking your DAX syntax doesn’t run your calculation and returns a rewarding No errors in formula output if everything was written correctly. Such a simple thing that can save you SO MUCH TIME! I highly recommend as a best practice to always use this before hitting ok and saving your measures, you’ll thank me later.
It makes for interesting reading.
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):
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.
There aren’t any performance benefits to doing this, although of course it helps with code readability and organisation (thanks to Marius for confirming this).
Even so, click through to see an example of how to do this.
My friend and coworker Melissa Coates (aka @sqlchick) messaged me the other day to see if I could help with a DAX formula. She had a Power BI dashboard in which she needed a very particular interaction to occur. She had slicers for geographic attributes such as Region and Territory, in addition to a chart that showed the percent of the regional total that each product type represented. The product type was in the fact/data table. Region and territory were in a dimension/lookup table and formed a hierarchy where a region was made up of one or more territories and each territory had only one region.
It’s rare to hear me say “MDX was easier” but in this case, MDX was easier…
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.
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.