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

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

Hi Anonymous,

 

How will this slicer work?

Why aren't the rows (Name 2, Name 9, Name 11) in the expected result?

 

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?