DAX Performance with multiple switch

[JohnnyDax]

Hi everyone,

I'm currently experiencing issue with a dataset : i can't load some visual, it's telling me that our capacity is at max memory (6144Mo) and what it's strange it's that until now it always worked. Also there is no logic about which visual can be loaded and which can't because it doesn't seem to be specific to a measure or a dimension.

Our IT doesn't want to expand our capacity and tell us that we may built our dataset wrong.

Let's say they're right, my last change was indeed about the main measure of the revenue. I wanted to have the possibility to switch between Revenue with taxe/without taxe then Raw revenue and Revenue net in this order. But without changing the whole dataset/report page, so i could just include two filter that would help me switch between those.

So i went from this :

Revenue = sum(revenue)

To this :

Revenue = 
SWITCH(SELECTEDVALUE(dim_taxe[CA]),
"HT",

SWITCH(SELECTEDVALUE(dim_ca[Type_CA]),
"CA Brut",'Groupe ticket aggrégé'[Ticket - Montant HT Brut],
"CA Net",'Groupe ticket aggrégé'[Ticket - Montant HT Net],
"CA Net Net",'Groupe ticket aggrégé'[Ticket - Montant HT Net]-[Ticket - Montant gratuit HT],
[Ticket - Montant HT net]-[Ticket - Montant gratuit HT]),

"TTC",
SWITCH(SELECTEDVALUE(dim_ca[Type_CA]),
"CA Brut",'Groupe ticket aggrégé'[Ticket - Montant TTC Brut],
"CA Net",'Groupe ticket aggrégé'[Ticket - Montant TTC Net],
"CA Net Net",'Groupe ticket aggrégé'[Ticket - Montant TTC Net]-[Ticket - Montant gratuit TTC],
[Ticket - Montant TTC net]-[Ticket - Montant gratuit TTC]),


SWITCH(SELECTEDVALUE(dim_ca[Type_CA]), // This last part is in case no filter is selected for the taxes
"CA Brut",'Groupe ticket aggrégé'[Ticket - Montant TTC Brut],
"CA Net",'Groupe ticket aggrégé'[Ticket - Montant TTC Net],
"CA Net Net",'Groupe ticket aggrégé'[Ticket - Montant TTC Net]-[Ticket - Montant gratuit TTC],
[Ticket - Montant TTC net]-[Ticket - Montant gratuit TTC]))

Is this significally less efficient or can it be fine ? The change is not recent and was made a month ago and it worked fine until now. No slowing was experienced either.

Just for you to have the whole picture here's how the dataset is made :

The issue is only experienced in the "Special Report Dataset", there is no error in the main one.

Thank you, i hope i'm clear enough

Best regards

[tamerj1 · Accepted solution]

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 )

[JohnnyDax]

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

[tamerj1]

@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.