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

 

jatneerjat's avatar
jatneerjat
Helper V
7 years ago

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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    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.

    • jatneerjat's avatar
      jatneerjat
      Helper V
      7 years ago

      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