Alternate solution to DAX

[shrimankabra]

Hi All,

I have written a dax function as shown below.

It works completely fine and give solution I require, the only problem is it take too much time to execute which decreases the performance of the report. I have been noticing this issue since I have added 'IF' condition as shown above.

Is there any alternative in this dax so that is take lesser time to execute than usual?

Note :- I can't replace "ALL(PATIENT_JOURNEY_2), VALUES(PATIENT_JOURNEY2_2[OPERATION]), VALUES(DIM_REGION[REGION])...." with ALLEXCEPT because ALLEXCEPT does not accept all columns from the table as an arguments and my dim tables have single columns.

Thanks in advance!

[Anonymous]

Hi @shrimankabra ,

According to your formula, it seems like you invoke many related tables as filter conditions.
When formula calculated on visuals, it will loop calculation on each row contents and affect performance.

Optimizing DAX expressions involving multiple measures

Optimizing nested iterators in DAX

You can consider to add a ranking column to data table instead cache these calculation in measure variables.

Regards,

XIaoxin Sheng

[shrimankabra]

Hi,

Thanks for the suggestion, but there's a problem with it, adding ranks to column would make it static and won't change dynamically when I apply slicer filters to it.