cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MarcParker Frequent Visitor
Frequent Visitor

DAX measure optimization

Hello everyone,

 

I have read several posts and some articles about the optimization of a Dax measure but I am having some issue to put these advices into fruition. I am trying to had a measure flagging Inventory Items (direct SSAS connection) into a table. Here is the measure i campe up with: 

Cluster = IF(SUM(INVENTORY[Annual_Usage_(Current_Year)])=0,"Obsolete",IF(DATEDIFF(SUM('LAST INVOICE DATE'[Date]),TODAY(),DAY)>=270,"Obsolete Warning",IF(SUM(INVENTORY[DSI])>=361 && SUM(INVENTORY[DSI])<=720,"Excess Warning",IF(SUM(INVENTORY[DSI])>=721,"Excess","Working"))))

 

 

Having a large amount of item, it takes ages to show the values. Could you help me with some tips on how I could enhance my measure formula?

 

Best regards,

 

Marc 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: DAX measure optimization

Hi @MarcParker 

 

It's worth using variables for any expression which is computed multiple times, that way, they're only calculated once.

 

I'd also reccommend using the SWITCH function in place of nested IF statements. It doesn't improve performance but is easier to read.


See if this helps at all:

Cluster =
VAR SumDSI = SUM ( INVENTORY[DSI] )
VAR Result =
    SWITCH (
        TRUE (),
        SUM ( INVENTORY[Annual_Usage_(Current_Year)] ) = 0, "Obsolete",
        DATEDIFF ( SUM ( 'LAST INVOICE DATE'[Date] ), TODAY (), DAY ) >= 270, "Obsolete Warning",
        SumDSI >= 361
            && SumDSI <= 720, "Excess Warning",
        SumDSI >= 721, "Excess",
        "Working"
    )
RETURN Result

 

Best regards,
Martyn


If I answered your question, please help others by accepting it as a solution.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Super User I
Super User I

Re: DAX measure optimization

Hi @MarcParker 

 

It's worth using variables for any expression which is computed multiple times, that way, they're only calculated once.

 

I'd also reccommend using the SWITCH function in place of nested IF statements. It doesn't improve performance but is easier to read.


See if this helps at all:

Cluster =
VAR SumDSI = SUM ( INVENTORY[DSI] )
VAR Result =
    SWITCH (
        TRUE (),
        SUM ( INVENTORY[Annual_Usage_(Current_Year)] ) = 0, "Obsolete",
        DATEDIFF ( SUM ( 'LAST INVOICE DATE'[Date] ), TODAY (), DAY ) >= 270, "Obsolete Warning",
        SumDSI >= 361
            && SumDSI <= 720, "Excess Warning",
        SumDSI >= 721, "Excess",
        "Working"
    )
RETURN Result

 

Best regards,
Martyn


If I answered your question, please help others by accepting it as a solution.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

MarcParker Frequent Visitor
Frequent Visitor

Re: DAX measure optimization

Yes it helps, thank you very much.

 

Marc 

dawnc63 Frequent Visitor
Frequent Visitor

Re: DAX measure optimization

I am having a similar problem, and I've read much of the posts here on optimization, but I must be missing something.
The only big fact table I have is a Date Table - everything else is based on measures and dates.  It was working just fine until I created this measure:

 

Reserve requirement =
 
VAR ReserveTable =
     SUMMARIZE( Dates,Dates[Year], "Reserve total", [Reserve requirement calc])

RETURN
     IF (HASONEVALUE(Dates[Year]), [Reserve requirement calc], SUMX(ReserveTable,[Reserve requirement calc])
     )

The [Reserve requirement calc] is based on more measures:
Reserve requirement calc =
  IF (
       [Contract Total increment] - [Tax revenue] > 0,
       [Contract Total increment] - [Tax revenue] ,
       BLANK()
 )


which was working fine until I added the [Reserve requirement] measure that provides the total.

 

Is there a way of optimizing this?  It is taking about 2 minutes to load the card visual and almost 3 minutes for the table visual.

 

Any tips would be so appreciated.  I'm finally catching on, but now that other people can see what is possible, I'm going to get more and more complicated problems to solve! 

 

MarcParker Frequent Visitor
Frequent Visitor

Re: DAX measure optimization

Hi,

 

I think you should open a new discussion as this one is marked completed.

 

Marc 

dawnc63 Frequent Visitor
Frequent Visitor

Re: DAX measure optimization

Thanks.  I've sort of solved this myself by creating a separate table with only January 1 of each year in the dates column, and using this table instead of the central dates table in the SUMMARIZE statement.  Now the refresh is down to about 30 seconds.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors