Need to optimize the DAX for better performance results instead of using Filter in DAX

[anee123]

Hi,

when I use Filter functions in DAX, the performance is too slow, when i remove those filters from DAX and put it in  Visual level filiter,

the performance is improved a lot. The problem with Visual level filters is that the calculation for Variance and Variance % is causing us an issue. We need this filters in DAX.  Is there someway to optimize this DAX?

Measure =
CALCULATE (
DISTINCTCOUNT(Test[activation_id]),
DATESBETWEEN(Test[Activation Date],date(2019,01,01), date(2019,12,31)),
FILTER(Test,Test[col1] ="ABC"),
FILter(Test,Test[col2]<> "DEF"),
filter(Test,Test[col3]<> "OTHER"))

[wdx223_Daniel · Accepted solution]

Measure =
CALCULATE (
DISTINCTCOUNT(Test[activation_id]),
DATESBETWEEN(Test[Activation Date],date(2019,01,01), date(2019,12,31)),
KEEPFILTERS(Test[col1] ="ABC"),
KEEPFILTERS(Test[col2]<> "DEF"),
KEEPFILTERS(Test[col3]<> "OTHER"))

[anee123]

Awesome, I was also thinking about keepfilters but didn't try that, I used the exact same formula and the performance is improved a lot, Thanks a lot for your quick help! Much appreciated...

[AnkitKukreja]

Hi @anee123 

Try and use them with Variables, please go through the link below for better clarity.

https://docs.microsoft.com/en-us/power-bi/guidance/dax-variables

Thanks,

Ankit

[anee123]

Thank you, I know the concept of varaiables, is there an alternate way to use other than Filter function in dax, filter function is too slow...