How to do Switch Statement for Multiple Columns Iteration

DAX DAX Calculations
3

@DavidNealon,

I was thinking along the same lines as @Melissa. If you unpivot your data and create a simple 1/0 dummy variable in Power Query, your DAX reduces down to two short measures:

Tests Passed = 

CALCULATE(
    SUM( 'Test Data Unpivoted'[Test Result Num]  ),
    ALLEXCEPT(
        'Test Data',
        'Test Data'[Trial   Number]
    ),
    TREATAS(
        VALUES( 'Test Data'[Trial   Number]),
        'Test Data Unpivoted'[Trial Number]
    )
)


Pass Fail = 

VAR TotalTests = 4

RETURN
IF( [Tests Passed] = TotalTests,
    "Pass",
    "Fail"
)

image

And this DAX will work for 4 tests or 4,000 tests, with the only change being to set the TotalTests variable to the proper number.

I hope this is helpful. Full solution file attached below.

1 Like