Optimize Dax Code

[Anonymous]

Hi,

I want to optimize below code, can you help me which DAX Functions should i use to optimize my dax code. 

VAR Test = Format(COALESCE(CALCULATE(SUM(Sales[Revenue]),

filter(Sales,Sales[product_status]="A" || Sales[product_status] = "C" || Sales[product_status]="Z" || Sales[product_status]="1"|| Sales[product_status]= ""),

FILTER(Sales,Product<>BLANK()),

FILTER(Sales,Sales[Sales_Date]>=Date(2022,04,01) && Sales[Sales_Date]<=Date(2022,04,30))),0),"general number")

Return Test

[Fowmy]

@Anonymous 

USe DAX Studio: Analyzing Performance Analyzer trace in DAX Studio - YouTube

[Fowmy · Accepted solution]

@Anonymous 

Please try following version. It's important to better understand the data model before performaing any DAX optimization.

VAR Test =
FORMAT (
    COALESCE (
        CALCULATE (
            SUM ( Sales[Revenue] ),
            KEEPFILTERS ( Sales[product_status] IN { "A", "C", "Z", "1", "" } ),
            KEEPFILTERS ( Sales[Product] <> BLANK () ),
            KEEPFILTERS ( Sales[Sales_Date] IN CALENDAR ( "2022-04-01", "2022-04-30" ) )
        ),
        0
    ),
    "general number"
)

[Anonymous]

@Fowmy  Thanks for reply, This is working good, but one more thing i want cross check, what is execution time of both DAX queries, how can we check this ?