Forum Discussion

ChPetru's avatar
ChPetru
Helper I
3 years ago

Poor performance IF measure

Hello,

 

I have created a measure that has horrible performance:

_Active License = 
VAR ExpiresInFuture =
    LASTDATE ( LineItem[Opportunity Line End Date] )
        >= LASTDATE ( ReferenceDate[Reference Date] )
VAR ExpiresInPast =
    LASTDATE ( LineItem[Opportunity Line End Date] )
        <= LASTDATE ( ReferenceDate[Reference Date] )
VAR StartsInFuture =
    FIRSTDATE ( LineItem[Opportunity Line Start Date] )
        >= LASTDATE ( ReferenceDate[Reference Date] )
VAR StartsInPast =
    FIRSTDATE ( LineItem[Opportunity Line Start Date] )
        <= LASTDATE ( ReferenceDate[Reference Date] )
RETURN
    IF (
        ExpiresInPast,
        "Expired",
        IF (
            StartsInPast && ExpiresInFuture,
            "Active",
            IF ( StartsInFuture && ExpiresInFuture, "Future", "Error" )
        )
    )

 

This is not even running for low granularity.. not sure how I can optimize it to run under 3ms. 

The output should tell me if a license if expired, active or future based on the row level start and end date.

 

Thank you

 

DAX
Formula
if
perofrmance
poor
powerbi
studio
switch

2 Replies

  • PabloDeheza's avatar
    PabloDeheza
    Solution Sage
    3 years ago

    Hi there!

    One thing you can try is splitting the variables so you dont make the engine compute the same thing multiple times. The engine is sometimes smart enough to realise that something was already computed but not always.

    Try one variable for 

    LASTDATE ( LineItem[Opportunity Line End Date] )

    One for

    LASTDATE ( ReferenceDate[Reference Date] )

    And one for

    FIRSTDATE ( LineItem[Opportunity Line Start Date] )

    Variables are only computed once, since you are using one of those expression 4 times, that should help at least something on your performance.

    Let me know if that helps! 

    • ChPetru's avatar
      ChPetru
      Helper I
      3 years ago

      Sorry, it did not help at all. I actually broke down the measure to:

       

      _License Active =
IF (
    FIRSTDATE ( LineItem[Opportunity Line Start Date] )
        <= LASTDATE ( ReferenceDate[Reference Date] )
        && LASTDATE ( LineItem[Opportunity Line End Date] )
            >= LASTDATE ( ReferenceDate[Reference Date] ),
    TRUE (),
    FALSE ()
)

       

      and it still has huge performance issues. 

      How can I optimize this?