topic Re: Quick calculation of a waterfall using multiple measures in DAX Commands and Tips

Quick calculation of a waterfall using multiple measures

j_78 — Tue, 10 Jan 2023 21:58:08 GMT

Hi everyone! 👋

It's been a few months since I discovered DAX, but I'm coming to you because I'm struggling with the creation of a visual, with a bad execution time for the moment.

Context:

I have created several complex metrics (to do a financial analysis by breaking down effects), but I am satisfied with the execution time (3 seconds max). Let's call them [Value1], [Value2], ..., [Value 14].

Objective:
I would like to create a waterfall chart from these measures. To do so, I followed the proposal of this link, by creating a workaround in DAX formula and a control table.

This gives the following measure:

SWITCH ( SELECTEDVALUE ( Table1[Index] ), 1, [Value 1], 2, [Value 1], 3, [Value 1], ..., 14, [Value 14], BLANK() )

Problem encountered:
I can make the desired waterfall. But the computation time is very long (around 30 seconds), whereas I can make appear in cards in less than 3 seconds all the measures I want to make appear in this waterfall.

Could you please give me some advice to improve the performance of my formula to create my waterfall (my hunch is that every measure is recalculated in my formula for the waterfall, resulting in an explosion of execution time)?

Thanks for your help! 🙂

(I can provide a file with anonymized data if my explanations are not clear)

Re: Quick calculation of a waterfall using multiple measures

AlexisOlson — Tue, 10 Jan 2023 23:21:50 GMT

There's a somewhat complicated way to do this with calculation groups that tends to be significantly better performance-wise.

For background, I recommend reading the following:
https://data-goblins.com/power-bi/measure-selection

https://www.sqlbi.com/articles/using-calculation-groups-to-selectively-replace-measures-in-dax-expressions/

I'm attaching a pbix where I've implemented this as described in the above articles along with a slightly simpler method that works as follows:

Define a new Calculation Group with each calculation item Value 1 = [Value 1], Value 2 = [Value 2], etc.

^^ Tabular Editor 2 ^^

You can drag them around to sort the order and it automatically creates an ordinal column for sorting. That starts at 0 though, so you can define a calculated column on the CalcGroup table as CalcGroup[Ordinal] + 1.

Now define the measure to replace your switching measure as

CALCULATE ( [DummyMeasure], CalcGroup )

It doesn't matter what measure you put in the first argument because it gets replaced when you use CalcGroup as filter context.

Now use the CalcGroup index and this new measure in your waterfall chart.

Re: Quick calculation of a waterfall using multiple measures

j_78 — Wed, 11 Jan 2023 15:44:19 GMT

Thank you very much AlexisOlson for your help.

I think I have understood, on a technical level - thanks to your file and the documentation provided - what to do. I'll try to implement this on my file and let you know when it's done!

Jacques