topic Re: Optimize Dax Code in DAX Commands and Tips

Optimize Dax Code

Anonymous — Sat, 06 May 2023 06:37:22 GMT

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

Re: Optimize Dax Code

Fowmy — Sat, 06 May 2023 07:23:07 GMT

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" )

Re: Optimize Dax Code

Anonymous — Sat, 06 May 2023 07:50:58 GMT

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 ?

Re: Optimize Dax Code

Fowmy — Tue, 09 May 2023 11:28:51 GMT

Anonymous

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