DAX: How do I write an IF statement to return a calculation for multiple (specific) values selected?

Question

This is driving me nuts. Let's say we want to use a slicer which has two distinct values to choose from a dimension. There is A and B.

Let us also say that my Fact table is connected to this dimension, however it has the same dimension with more options.

My slicer now has A, B and (Blank). No biggie.

Let's now say I want to list out all of the possible calculation outcomes by selecting the slicer in a DAX formula, but in my visual I need all those outcomes to be listed in an IF() branched formula:

I can list out A:

IF(MAX(SlicerDim[Column]) = "A", CALCULATE([Calculation], SlicerDim[Column] = "A")

I can list out B:

IF(MAX(SlicerDim[Column]) = "A", CALCULATE([Calculation], SlicerDim[Column] = "A")

I can list out the (Blank) calculation too:

CALCULATE([Calculation], SlicerDim[Column] = Blank())

And I've managed to get a calculation out of it even when all of the slicer elements are on or off, using:

NOT(ISFILTERED(SlicerDim[Column])), CALCULATE([Calculation], SlicerDim[Column] = "A" || SlicerDim[Column] = "B")

Notice I need this IF() branch to actually return a calculation using A & B values, so now I have returns for when A or B or (Blank) or All or None are selected; BUT NOT when multiple values of A & B are selected!

How do I write out this IF() branch for it to return the same thing, but when both A & B are selected? Since there are only two real options in the slicer - I managed to use MIN() and MAX() get it to work by using their names or Index numbers.

IF((MIN(SlicerDim[Column]) = "A" && MAX(SlicerDim[Column]) = "B") || NOT(ISFILTERED(Paslauga[Paslauga])), CALCULATE([Calculation], SlicerDim[Column] = "A" || SlicerDim[Column] = "B")

BUT - I want a more understandable/robust/reusable formula, so that I could list out many selectable values from the slicer and have it return a calculation for specifically selected slicer values.

Please, help. I've been searching high and low and there seems to not be an easy way to fix this albeit scraping the IF route and just using a damn slicer for this type of dilemma.

TL;DR: How do I write an IF() branch calculation using DAX to get an outcome when All/None or non-blank or Specific slicer values are selected?

My best effort: I am looking to improve the first IF() branch to not have to use MIN/MAX, because I would like to be able to reuse this type of formula if there were more than two real options in the slicer:

IF_branch = 
IF((MIN(SlicerDim[Column]) = "A" && MAX(SlicerDim[Column]) = "B" || NOT(ISFILTERED(SlicerDim[Column])), CALCULATE([Calculation], SlicerDim[Column] = "A" || SlicerDim[Column] = "B"),
IF(MAX(SlicerDim[Column]) = "A", CALCULATE([Calculation], SlicerDim[Column] = "A"),
IF(MAX(SlicerDim[Column]) = "B", CALCULATE([Calculation], SlicerDim[Column] = "B"),
CALCULATE([Calculation], SlicerDim[Column] = BLANK()))))

mxix mxix · Accepted Answer · 2019-03-08T12:09:19

Think what you are looking for is CONTAINS and VALUES

VALUES will give you the distinct current selection in scope. CONTAINS lets you check if a table contains any row with a set of values.

[ Example ]

Formulas:

selected Scenarios = CONCATENATEX(VALUES(DimScenario[ScenarioName]);[ScenarioName];";")

Contains Forecast and Budget? = 
    IF(
        CONTAINS(VALUES(DimScenario[ScenarioName]);[ScenarioName];"Forecast") &&
        CONTAINS(VALUES(DimScenario[ScenarioName]);[ScenarioName];"Budget")
        ;"Yes"
        ;"No"
    )

DAX: How do I write an IF statement to return a calculation for multiple (specific) values selected?

1 Answers