Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]))
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?
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
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?
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]))
No, it doesn't look optimized at all quite frankly, but I can't determine if there is a better way of doing it without running some tests on some sample data. So, either provide that or I can't really help you.
The Userid and appid both are nvarchar and having 32 digit unique identifier.this table have total 20 columns and millions of records.
In this table i have 6 ID columns which are having type integer,2 date columns and 7 string columns.
Thanks
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |