Forum Discussion

Rocon's avatar
Rocon
Frequent Visitor
2 years ago

Switch function

Hi, I have some trouble with a switch function that operates very slow and I think it's due to an Allselected in some measures but don't know how to fix it, so would be so gelreatful for some help. ...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Rocon 

 

Using the Allselected function in the Calculate function does affect performance, especially when working with large data sets or complex calculations.

 

This is because Allselected retains all filters except those on the rows and columns of the table that uses it, which can lead to a lot of recalculations when the context changes.

 

ALLSELECTED function (DAX) - DAX | Microsoft Learn

 

You can try the following methods to improve performance:


Simplify the calculation logic as much as possible and make sure that only what is necessary is calculated.

 

If MEASURE is complex, consider using variables to store intermediate results.

 

Using variables in your DAX formulas can help you write more complex and efficient calculations. Variables can improve performance, reliability, readability, and reduce complexity.

 

Use variables to improve your DAX formulas - DAX | Microsoft Learn

 

If performance issues are indeed related to the use of Allsselected, please consider whether you need to use it in all cases.

 

Use the performance analyzer in Power BI Desktop to determine which visual objects or metrics take the longest to load.

 

Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Microsoft Learn

 

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Rocon's avatar
Rocon
Frequent Visitor
2 years ago

Thank you so much for your answer Nuno!

 

The thing is that the calculations is not that advanced but since I have YTD, YTD FC, YTD LY, Month etc it seems like it takes a lot of the performance. I have a measure like this where I use ALLSELECTED but when I remove the ALLSELECTED from the formula, no value is being displayed: 

Absence (Short term) % =
CALCULATE(
    DIVIDE([Absence (Short term) Total FTE],
    ([Active Direct FTE (Exc Temps)]+[Active Indirect FTE (Exc Temps)]),0),
        ALLSELECTED(Dim_Attributes[ID])
    ). 
 
I have these measures that formats the current measure in the period type I want: 
Month AC =
VAR CurrentValue = CALCULATE([*Switch_value], Selection_VersionType[AC_FC]="AC")
VAR FormatString = LOOKUPVALUE(
    Dim_Attributes[Displayed format],
    Dim_Attributes[Sub group],
    SELECTEDVALUE(Dim_Attributes[Sub group])
)
RETURN
FORMAT(CurrentValue, FormatString).
 
Everything is connected to a Dimension table for the specifications, but I can't find a good solution for this or if the formatting and ALLSELECTED function is something I have to work with to get the numbers ok. For the above, I don't have Absence % in any data and only get this from the calculation.
 
Thanks!
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Rocon 

     

    Can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

     

    Regards,

    Nono Chen