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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Stemar_Aubert
Resolver I
Resolver I

Optimize multiple variables in DAX IF statements

Hello,

 

I have a measure that captures the slicer value selected, then returns a specific scenario which rankx my data.

 

 

AlgoSelectivePINS = 
VAR _SlicerValueLow = 6
VAR _SlicerValueHigh = 7
VAR _ScenarioA =
    IF (
        HASONEVALUE ( Local[DISTRICT] ),
        CALCULATE (
            RANKX (
                FILTER ( ALL ( Local ), Local[SelectNoChina] = 0 ),
                FPins[PINS YTD YoY Δ]
            ),
            ALLEXCEPT ( Local, Local[DISTRICT] )
        ),
        "-"
    )
VAR _ScenarioB =
    IF (
        HASONEVALUE ( Local[DISTRICT] ),
        CALCULATE (
            RANKX ( FILTER ( ALL ( Local ), Local[SelectNoGP] = 0 ), FPins[PINS YTD YoY Δ] ),
            ALLEXCEPT ( Local, Local[DISTRICT] )
        ),
        "-"
    )
VAR _ScenarioC =
    IF (
        HASONEVALUE ( Local[DISTRICT] ),
        CALCULATE (
            RANKX ( FILTER ( ALL ( Local ), Local[SelectAll] = 0 ), FPins[PINS YTD YoY Δ] ),
            ALLEXCEPT ( Local, Local[DISTRICT] )
        ),
        "-"
    )
VAR _ScenarioD =
    IF (
        HASONEVALUE ( Local[DISTRICT] ),
        CALCULATE (
            RANKX ( FILTER ( ALL ( Local ), Local[SelectNone] = 0 ), FPins[PINS YTD YoY Δ] ),
            ALLEXCEPT ( Local, Local[DISTRICT] )
        ),
        "-"
    )
VAR _ScenarioSelectorA =
    IF (
        [SlicerCaptureSum] < _SlicerValueLow,
        _ScenarioB,
        IF ( [SlicerCaptureSum] < _SlicerValueHigh, _ScenarioA, _ScenarioC )
    )
VAR _ScenarioSelectorB =
    IF ( [SlicerCaptureSum] = _SlicerValueLow, _ScenarioD, _ScenarioB )
RETURN
    IF (
        'Biais Switch'[BiaisCaptureSum] = 0,
        _ScenarioSelectorA,
        _ScenarioSelectorB
    )

 

 

I would like to know if switching my HASONEVALUE() evaluation with instead a variable, such as VAR _DistrictValueCheck = HASONEVALUE(Local[DISTRICT]) would work better than having, for each scenario, the evaluation you see in my code above.

 

Thanks !

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@Stemar_Aubert 

It would help a bit I think, the new VAR would only evaluate once as opposed to each time.  You should take a look at DAX studio which can help you judge the improvement you would see from the change.

https://www.sqlbi.com/tools/dax-studio/

It lets you modify and test measures locally in the tool without changing your model until you are ready.

View solution in original post

1 REPLY 1
jdbuchanan71
Super User
Super User

@Stemar_Aubert 

It would help a bit I think, the new VAR would only evaluate once as opposed to each time.  You should take a look at DAX studio which can help you judge the improvement you would see from the change.

https://www.sqlbi.com/tools/dax-studio/

It lets you modify and test measures locally in the tool without changing your model until you are ready.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors