Need ideas optimizing a long measure

[RingoMoon]

Contacts roles per Org Prior = 
VAR SelectedEditions = VALUES(opp_data[eventname])
VAR Result =
    SUMX(
        SelectedEditions,
        VAR SelectedEvent = opp_data[eventname]
        VAR SelectedEdition = SELECTEDVALUE(opp_data[eventeditioncode])
        VAR LastNonBlankEdition = 
            CALCULATE(
                SELECTEDVALUE('Prior Editions'[PriorEdition]),
                opp_data[eventname] = SelectedEvent
            )
        VAR EditionDiff = 
            SelectedEdition - LastNonBlankEdition
        RETURN
            IF(
                ISBLANK(LastNonBlankEdition),
                BLANK(),
                CALCULATE(
                    SUM(opp_data[no_of_contact_roles]),
                    opp_data[eventeditioncode] = LastNonBlankEdition && opp_data[eventname] = SelectedEvent, activity_data[activitydate]<(TODAY()-(EditionDiff*365))
                )
            )
    )
VAR Result2 = 
SUMX(
SelectedEditions,
        VAR SelectedEvent = opp_data[eventname]
        VAR SelectedEdition = SELECTEDVALUE(opp_data[eventeditioncode])
        VAR LastNonBlankEdition = 
            CALCULATE(
                SELECTEDVALUE('Prior Editions'[PriorEdition]),
                opp_data[eventname] = SelectedEvent
            )
        VAR EditionDiff = 
            SelectedEdition - LastNonBlankEdition
        RETURN
            IF(
                ISBLANK(LastNonBlankEdition),
                BLANK(),
                CALCULATE(
                    DISTINCTCOUNTNOBLANK('opp_data'[org_id]),
                    opp_data[eventeditioncode] = LastNonBlankEdition && opp_data[eventname] = SelectedEvent, opp_data[createdondt]<(TODAY()-(EditionDiff*365))
                )
            )
    )
RETURN
CALCULATE(
    Result / Result2,
    opp_data[opppipelinestgdesc] <> "NOT_STARTED"
)

I have a couple of measures with a format similar to the above that's in a table visual. My dataset is very large and as you can imagine, I get an error on my table visual saying it has exceeded the available resources and the loading time is very long for other card visuals containing these measures.

Originally both the LastNonBlankEdition variables were like below, and the visuals were able to load just fine. But keeping it like this resulted in an issue where the measure returns blank if nothing is selected in an opp_data[eventname] slicer. That's why I had to enclose it inside a CALCULATE function and set the filter to opp_data[eventname] = SelectedEvent because that gives me an actual value when nothing is selected in the eventname slicer. But that has increased the loading time and fails to load on the table visual I mentioned earlier.

VAR LastNonBlankEdition = 
SELECTEDVALUE('Prior Editions'[PriorEdition])

Also, I had to have the SUMX functions have their own separate variables because that was the only way I could get the correct number by having the SUMXs independent and calculate the numerator and denominator within their own context

Is there any other way I can optimize this measure?

[v-tangjie-msft]

Hi @RingoMoon ,

You mean you don't want LastNonBlankEdition to be null but a fixed value even if eventname's slicer doesn't have any value selected, right?
Then you can use the MAX function instead of the SELECTEDVALUE function, the difference is that the MAX function will not be null when there is no selected value in the slicer.
Then you can modify the DAX function to:

...
VAR SelectedEvent = MAX(opp_data[eventname])
VAR SelectedEdition = SELECTEDVALUE(opp_data[eventeditioncode])
VAR LastNonBlankEdition = 
            CALCULATE(
                MAX('Prior Editions'[PriorEdition]),
                opp_data[eventname] = SelectedEvent
            )
...

Or, you could just write that the variable LastNonBlankEdition is assigned a fixed value if it is null. I think the IF function would be helpful. Then you could modify the DAX function to:

...
VAR SelectedEvent = SELECTEDVALUE(opp_data[eventname])
VAR SelectedEdition = SELECTEDVALUE(opp_data[eventeditioncode])
VAR LastNonBlankEdition = 
IF(ISBLANK( SELECTEDVALUE('Prior Editions'[PriorEdition])),2,SELECTEDVALUE('Prior Editions'[PriorEdition]))  //Here I assume that the fixed value is 2
...

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.