Optimize DAX - Filters

[Anonymous]

Hello everyone! How are you?

Taking advantage of the Spanish forum, I make the following related query to optimize query performance:

I have a measure that has a SWITCH with 10 IF (each with its condition) and a variable with a FILTER that is applied to the SWITCH CALCULATE:

VALUE=
VAR KPIS =
FILTER (
Table_1;
 Table_1[ID_Account] = MAX ( Table_2[KPI_Account] )
)

VAR valor =
SWITCH (
Index;
 1; IF (Condition); 
2; IF (Condition); 
3; IF (Condition); 
4; IF (Condition); 
5; IF (Condition); 
6; IF (Condition); 
7; IF (Condition); 
8; IF (Condition); 
9; IF (Condition); 
10; IF (Condition); 
FORMAT ( CALCULATE ( [Actual]; KPIS ); formato )
)
RETURN
valor

I'm trying to optimize the query, for this I wanted to start by the FILTER of the KPIS variable.

According to what I have been reading the ideal is to apply a KEEPFILTER ( FILTER ( ALL, in case in the filter we are using a single table, but in my case I am using two different tables.

Does anyone have any recommendations to optimize the FILTER part? Maybe adding a CROSSJOIN?

Thanks a lot!

Best regards!

[Anonymous]

Hi @Anonymous ，

In order to better understand your requirement, could you please provide some sample data and the results you want? Also please explain what are the condition and function of the formula which need to be optimized? Then we can provide you with appropriate suggestion or formula based on your scenario. Thank you.

Best Regards

Rena

[Greg_Deckler]

I do not see an obvious way to improve that filter. Perhaps more information would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490