Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jatneerjat
Helper V
Helper V

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 10
v-frfei-msft
Community Support
Community Support

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

@v-frfei-msft

 

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

 

Thanks

@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?

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 

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?

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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]))

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler

 

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

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.