DAX Measure: Slicer using 2 columns

Question

I have two columns with numbers from 1 to 10, for example:

Column A	Column B
1	2
3	1

I have to create a slicer that will filer other columns (Column C, Column D....) based on the numbers in Column A and B like this:

If there is 1 in a row from Column A or in Column B, show a row from Column C and Column D.

So the slicer should filter the rest of the columns based on the fact if those columns have a number that is contained in Column A OR in Column B.

I searched for the solutions, but people usually recommend creating a table with those two columns, but that is over the top for me. I tried with switch function, but it is not working properly aka not good for the slicer and slicer is the only option for filtering for me.

Answer 1

Ultimately, the slicer will need to use a column from a different table. Let's help you do so.

In Report View, select New Table in the Modeling tab in the ribbon, and paste in one of these expressions:

// if you want all the unique values of column A and B in the slicer:
Slicer A B = 
  SELECTCOLUMNS(
    DISTINCT( UNION( DISTINCT(YourTable[Column A]), DISTINCT(YourTable[Column B]) ) ),
    "Slicer", [Column A]
  )

// Or if you want 1 to 10, then use this:
Slicer A B = 
  SELECTCOLUMNS(
    GENERATESERIES(1, 10, 1),
    "Slicer", [Value]
  )

Next, create a new Measure with:

Count A B = 
  CALCULATE(
    COUNTROWS(YourTable), 
    YourTable[Column A] IN VALUES('Slicer A B'[Slicer]) || 
    YourTable[Column B] IN VALUES('Slicer A B'[Slicer])
  )

Now add a slicer from 'Slicer A B'[Slicer] column, and in the Table visual, add a Visual Filter with Count A B is not blank.

And that should be it.