Forum Discussion

jatneerjat's avatar
jatneerjat
Helper V
7 years ago

How to optimize below DAX

Hi TomMartens Ashish_Mathur Zubair_Muhammad  Greg_Deckler   How can i optimize below DAX as it currently affect the perfomance and the 'card with states' visual takes time to load.   AvgVal:= IF(...
Need Help
jatneerjat's avatar
jatneerjat
Helper V
7 years ago

v-frfei-msft

 

How to replace it.Can I replace AVGX and recreate this measure?

 

Thanks

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

jatneerjat - At a basic level, I take it your data looks like:

 

UserTable

- UserID

 

 

ApplicationTable

- UserID

- ApplicationID

 

 

And you have a relationship between user and application tables. You have 3 user tables and 3 application tables.

 

Is that all correct?

 

 

  • jatneerjat's avatar
    jatneerjat
    Helper V
    7 years ago

    Hi Greg_Deckler

     

    By mistake i wrote wrong measure,below is the correct measure.where uid and Applicationid are in the same table [viewUser1] which have  12 other columns.

     

    AvgVal:= IF(HASONEVALUE(Table1[Id])
    ,SWITCH(VALUES(Table1[Id])
    ,1,[AvgColumn1]
    ,2,[AvgApplication28]
    ,3,[AvgApplication7]
    )
    )

     

     

    Where  columns used in switch are below:

     

     

    AvgColumn1:= AVERAGEX(
    KEEPFILTERS(VALUES('viewUser1'[UId])),
    CALCULATE(DISTINCTCOUNT('viewUser1'[ApplicationId]))

     

    AvgApplication28:= CALCULATE([AvgApplication],DATESBETWEEN(viewDate[Date]
    ,LASTDATE(viewDate[Date])-27
    ,LASTDATE(viewDate[Date])
    ))

     


    AvgApplication7:= CALCULATE([AvgApplication],DATESBETWEEN(viewDate[Date]
    ,LASTDATE(viewDate[Date])-6
    ,LASTDATE(viewDate[Date])
    ))

    Ui

     

     

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      7 years ago

      OK, so that changes things quite a bit overall actually. Let's work on the first one, does your data look like:

       

      UId,ApplicationId

      User1,App1

      User1,App2

      User1,App3

      User1,App1

      User1,App2

      User1,App3

      User2,App1

      User3,App1

       

      So, User1 has 4 distinct apps, users 2 and 3 each have 1 so your measure AvgColumn1 (I assume that it is actually a measure, please correct if not) would return 2 (4+1+1)/3 = 2. Correct?

       

      • jatneerjat's avatar
        jatneerjat
        Helper V
        7 years ago

        Hi Greg_Deckler

         

        Let me keep it more simple.I just want to optimize below statement,is it possible or it is already in a optimized way?

        What is KEEPFILTERS and VALUES achieving here?

        I just somehow want to replace AVERAGEX() with some other function.

         

        AvgColumn1:= AVERAGEX(
        KEEPFILTERS(VALUES('viewUser1'[UId])),
        CALCULATE(DISTINCTCOUNT('viewUser1'[ApplicationId]))