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
v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi jatneerjat,

 

Maybe you can use measures to replace calculated column to work on it. Kindly share your pbix or sample data and excepted result to me, so that I can help with it.

 

Regards,

Frank

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?