Forum Discussion
DAX Performance with multiple switch
Hi JohnnyDax
Please tryRevenue = VAR A = SELECTEDVALUE ( dim_taxe[CA] ) VAR B = [Ticket - Montant HT Brut] VAR C = [Ticket - Montant HT Net] VAR D = [Ticket - Montant gratuit HT] VAR E = [Ticket - Montant TTC Brut] VAR F = [Ticket - Montant TTC Net] VAR G = [Ticket - Montant gratuit TTC] VAR X = SWITCH ( A, "CA Brut", B, "CA Net", C, C - D ) VAR Y = SWITCH ( A, "CA Brut", E, "CA Net", F, F - G ) RETURN SWITCH ( A, "HT", X, Y )
Hi JohnnyDax
Please try
Revenue =
VAR A =
SELECTEDVALUE ( dim_taxe[CA] )
VAR B = [Ticket - Montant HT Brut]
VAR C = [Ticket - Montant HT Net]
VAR D = [Ticket - Montant gratuit HT]
VAR E = [Ticket - Montant TTC Brut]
VAR F = [Ticket - Montant TTC Net]
VAR G = [Ticket - Montant gratuit TTC]
VAR X =
SWITCH ( A, "CA Brut", B, "CA Net", C, C - D )
VAR Y =
SWITCH ( A, "CA Brut", E, "CA Net", F, F - G )
RETURN
SWITCH ( A, "HT", X, Y )
Hi tamerj1 ,
Thank you for your answer, according to the perf analyzer it seems to be at least 2x more efficient in the main dataset
Unfortunatly it doesn't fix on the "special dataset" but this may because i have other function (like the growth) that has been written not using your method. I'll try to convert every one of them to this one and see if it helps.
Just for my culture, does this methode mean PowerBI won't calculate the variables if the condition aren't met ? (if i don't choose "HT" it won't calculate in background "HT formula")
Thank you
JohnnyDax
In fact it dose not in both method but this is not the difference.
In fact very time the same measure is called inside a DAX code it will be recalculated unless it is stored in a variable which guarantees that the measure won't be computed more than once. I also I noticed unnecessary repetance of calculations which I just simplified.