cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohnnyDax
Regular Visitor

DAX Performance with multiple switch

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 :

save.png

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

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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 )

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors