Forum Discussion
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(HASONEVALUE(Table1[Id])
,SWITCH(VALUES(Table1[Id])
,1,[AvgColumn1]
,2,[AvgColumn2]
,3,[AvgColumn3]
)
)
Where columns used in switch are below:
AvgColumn1:= AVERAGEX(
KEEPFILTERS(VALUES('viewUser1'[UId])),
CALCULATE(DISTINCTCOUNT('viewApp1'[ApplicationId]))
AvgColumn2:= AVERAGEX(
KEEPFILTERS(VALUES('viewUser2'[UId])),
CALCULATE(DISTINCTCOUNT('viewApp2'[ApplicationId]))
AvgColumn3:= AVERAGEX(
KEEPFILTERS(VALUES('viewUser3'[UId])),
CALCULATE(DISTINCTCOUNT('viewApp3'[ApplicationId]))
10 Replies
Can take a look at seeing how to perhaps optimize your DAX, but would probably be much more successful if we had a sense of your source data and what you were trying to accomplish. That way we could recreate it and test out some different approaches.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi Greg_Deckler
I am using the below dax in a measure in analysis service and using this measure in a 'Card with states' visual but while i refresh this report then it takes a lot of time to load so it might be issue with the dax that have been written.
I also posted this one related to it:
https://community.powerbi.com/t5/Service/visuals-are-loading-very-slow-in-power-bi/m-p/522423#M53870
Thanks
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 - 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?
