Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Optimize Dax Code

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
Help
  • Fowmy's avatar
    Fowmy
    3 years ago

     

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



3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    3 years ago

     

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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 ?