Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help Using One Slicer for 3 columns

I am trying to filter my data with 1 slicer across 3 columns.  The data I have looks like this:

 

ProjectColor 1Color 2Color 3
1RedBlueGreen
2GreenYellow 
3BlueGreen 
4YellowBlue 
5GreenRedYellow
6Yellow  

 

I would like the slicer to be:  Red, Green, Blue, Yellow, so if Red was selected, Project 1&5 would show up, if Blue was selected, Project 1,3 & 4, etc. 

 

Is there a way to do this? 

 

Thanks

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous ,

     

    Please download the demo from the attachment and try it.

    1. Create a new table. DO NOT establish any relationship.

    Colors =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( Table1[Color 1] ),
            VALUES ( Table1[Color 2] ),
            VALUES ( Table1[Color 3] )
        )
    ),
    [Color 1] <> BLANK ()
)

    2. Create a measure.

    Measure =
VAR _colors =
    VALUES ( Colors[Color] )
RETURN
    IF (
        MIN ( Table1[Color 1] ) IN _colors
            || MIN ( Table1[Color 2] ) IN _colors
            || MIN ( Table1[Color 3] ) IN _colors,
        1,
        BLANK ()
    )

    3. Add the measure into the Visual Level Filter and filter out the blanks.

    Help-Using-One-Slicer-for-3-columns

     

    Best Regards,

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous 

     

    Hi, Try unpivoting the data in Query Editor.

     

    Regards

     

    Victor

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks - this is actually part of a larger data set that is currently 3,100 rows and counting and about 30-40 columns total.  Could I create a new table with just these columns and unpivot that?  Maybe a separate query that just pulls in these 4 columns and then unpivots?

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous ,

     

    Please download the demo from the attachment and try it.

    1. Create a new table. DO NOT establish any relationship.

    Colors =
FILTER (
    DISTINCT (
        UNION (
            VALUES ( Table1[Color 1] ),
            VALUES ( Table1[Color 2] ),
            VALUES ( Table1[Color 3] )
        )
    ),
    [Color 1] <> BLANK ()
)

    2. Create a measure.

    Measure =
VAR _colors =
    VALUES ( Colors[Color] )
RETURN
    IF (
        MIN ( Table1[Color 1] ) IN _colors
            || MIN ( Table1[Color 2] ) IN _colors
            || MIN ( Table1[Color 3] ) IN _colors,
        1,
        BLANK ()
    )

    3. Add the measure into the Visual Level Filter and filter out the blanks.

    Help-Using-One-Slicer-for-3-columns

     

    Best Regards,