DAX SWITCH change a value based on another column and a slicer value

Question

I am familiar with using a slicer with a SELECTEDVALUE in a SWITCH statement to change values, but I also need to do it based on data in another column.

Number  Letter   EW
  1       A     East
  2       A     East
  3       P     West
  4       P     West

From the above table, I want to dynamically change the value of the 'EW' column to East or West based on a slicer selection. Each letter has it's own slicer. One for A, one for P. Each of these slicers lets you pick East or West.

So for the 'A' slicer, say I pick 'West'. Every row in the table where Letter = 'A' would change the 'EW' value to West.

I have used the following code in the past to allow data to change based on a slicer selection, but it does not help me when I need to change the EW column based on the Letter column.

NewEW = SWITCH(
    SELECTEDVALUE(Letter[Letter]),
     "A", "East",
     "West")

JBfreefolks JBfreefolks · Accepted Answer · 2019-10-01T07:44:08

As far as I understand your table come with two supporting table (Driving slicers) :

Then a solution is to set this measure :

NewEW = 
VAR VrCurrRow = SELECTEDVALUE(Letter[Letter];"")
VAR VrSlicerA = 
CALCULATE(
     SELECTEDVALUE('Slicer A'[EW];"")
)
VAR VrSlicerB = 
CALCULATE(
     SELECTEDVALUE('Slicer B'[EW];"")
)
VAR Result =

 SWITCH(
    VrCurrRow;
    "A";VrSlicerA;
    VrSlicerB
 )
RETURN
Result

Result :

DAX SWITCH change a value based on another column and a slicer value

1 Answers