topic Switch function in DAX Commands and Tips

Switch function

Rocon — Mon, 01 Apr 2024 21:11:19 GMT

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.

I have several data tables and have created measures for a number of vales I want to show in a matrix for month, ytd etc with some formatting for each measures how they shall be viewed. For the ones I have direct data to there's no issue, but for some I have created a measure based on two different measures, for example FTE absence divided by FTE to get a dynanic percentage. If I have this measure directly in the matrix via a switch function no values are displayed, but I have read about using allselected in a calculate function and then it works, but this is taking so long time when I change month for example. I have a spec dimension table that I'm using for the switch and matrix and have added all spec names in this table, including these that has no direct data but where I get the numbers thru two measures.

s there another way of doing this smoothly and more efficient and hope the above makes sense? I also have this issue in an income statement where I use Allselected in my functions for the Ebit etc where I use that function for a dimension table for the rows from the income statement.

Many thanks in advance!

Re: Switch function

Anonymous — Tue, 02 Apr 2024 01:20:10 GMT

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.

Re: Switch function

Rocon — Thu, 04 Apr 2024 14:17:56 GMT

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!

Re: Switch function

Anonymous — Thu, 11 Apr 2024 07:53:06 GMT

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