Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Once Slicer for multiple columns

Hi, I am trying to create a single slicer for the below table, i would like to see all of the table when i first load the report, but when i come to slicer "Part One/Part Two/Part Three/Part Four/Par...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    There is a solution below. Please check out the demo in the attachment.

    1. Create a new table as slicer table.

    SlicerTable =
DISTINCT (
    UNION (
        VALUES ( Table1[Part One] ),
        VALUES ( Table1[Part Two] ),
        VALUES ( Table1[Part Three] ),
        VALUES ( Table1[Part Four] ),
        VALUES ( Table1[Part Five] )
    )
)

    2. Rename the column name of the new table. (optional)

    3. Do not establish any relationships!

    4. Create a measure.

    Measure =
IF (
    MIN ( 'Table1'[Part One] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Two] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Three] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Four] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Five] ) IN VALUES ( SlicerTable[values] ),
    1,
    BLANK ()
)

    Once_Slicer_for_multiple_columns

     

    Best Regards,

    Dale

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hello Dale,

 

Sorry to the late response, Seems that it did not copy correct!

 

It should look like this;

 

ThreatNodeActivityPart OnePart TwoPart ThreePart FourPart Five
Area OneDownName 1 Base Management Production ShopProduction Shop
Area OneDownName 2Base ManagementProduction ShopFlow AssuranceProduction ShopProduction Shop
Area OneUpName 3 Production ShopBase ManagementProduction ShopFlow Assurance
RoadRightName 8 Base ManagementProduction ShopBase ManagementCorrosion
RoadRightName 9Base ManagementGatewayFlow AssuranceCorrosionCorrosion
Upper DeckRightName 10 Base ManagementProduction ShopBase ManagementFlow Assurance
Upper DeckRightName 11Base ManagementProduction ChemistProcess EngineerArea OpsFlow Assurance
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Even if it is not a slicer, can this be done in a table as show with filters?

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    There is a solution below. Please check out the demo in the attachment.

    1. Create a new table as slicer table.

    SlicerTable =
DISTINCT (
    UNION (
        VALUES ( Table1[Part One] ),
        VALUES ( Table1[Part Two] ),
        VALUES ( Table1[Part Three] ),
        VALUES ( Table1[Part Four] ),
        VALUES ( Table1[Part Five] )
    )
)

    2. Rename the column name of the new table. (optional)

    3. Do not establish any relationships!

    4. Create a measure.

    Measure =
IF (
    MIN ( 'Table1'[Part One] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Two] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Three] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Four] ) IN VALUES ( SlicerTable[values] )
        || MIN ( 'Table1'[Part Five] ) IN VALUES ( SlicerTable[values] ),
    1,
    BLANK ()
)

    Once_Slicer_for_multiple_columns

     

    Best Regards,

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      This solution is quite elegant and is exactly what I am looking for.

      Now a twist. What if the data is coded and converted via links and relationships? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Is this only available for tables? What if I wanted to use this for a stacked column chart?

    • selpaqm's avatar
      selpaqm
      Helper V
      6 years ago
      Hi v-jiascu-msft , your solution is worked for extended version 6 different column. unless, it takes ages when i click the slicer. is there any faster way? Regards,