Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Optimize DAX - Filters

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!

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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