Forum Discussion

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(...

Community Support

7 years ago

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
Helper V
7 years ago
v-frfei-msft

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

Thanks
- 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
    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