cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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/Part Five" rather than having a slicer for each one, am i able to create just one so i can show all the data related to them? I already have slicer for Threat and node that work fine.

 

Base table

 

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
Area OneUpName 4 Production ShopProcess EngineerArea OpsProduction Shop
Area OneUpName 5 Flow AssuranceProduction ShopArea OpsFlow Assurance
Area OneLeftName 6 Production ShopFlow AssuranceProduction ChemistProduction Shop
Area OneLeftName 7 Production ShopFlow AssuranceProcess EngineerProduction Shop
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

 

 

How i would like it to look once i have selected "base management" in the slicer;

 

ThreatNodeActivityPart OnePart TwoPart ThreePart FourPart Five
Area OneDownName 1 Base Management Production ShopProduction Shop
Area OneUpName 3 Production ShopBase ManagementProduction ShopFlow Assurance
RoadRightName 8 Base ManagementProduction ShopBase ManagementCorrosion
Upper DeckRightName 10 Base ManagementProduction ShopBase ManagementFlow Assurance

 

 

Thanks

1 ACCEPTED SOLUTION

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

15 REPLIES 15
anna_lucia
Regular Visitor

Hi there,

 

I am trying to implement a similar slicer and was already able to implement this solution. However, I would like to be able to select multiple values in the slicer and then have it display only the rows that posses all these functions, and not just one of them as it does now. 

 

For example if in this case I would select 'base management' and 'production shope' it would only display the 1st, 3rd and 8th row.

 

Thanks in advance! 

Hi Anna,

 

Were you able to find a solution to this problem? I am running into the same issue.

 

Thanks!

 

https://community.powerbi.com/t5/Desktop/Distinct-Count-Based-on-Multiple-Selections-of-a-Slicer-Cre...

Unfortunately, I haven't. I'll be working on it again today and if I figure anything out I'll share it here and on your thread. Cheers! Anna
Artie
Frequent Visitor

Hi there,

Just thinking outloud.  Have you tried unpivoting the data?

 

Perhaps you can come with a table with the following columns:

Threat, Node,Activity, Part, Code (value).

 

Then make up a matrix visual and use column "Part" as the column header.

 

The only "inconvenience" for your users is that the matrix would be dynamic, would show columns only when there is information.  The other one is that data can not be exported to Excel in a tabular way but linear (most of users want to see the exported data as it shows in Power Bi).

 

Regards.

Hi Artie, This does work partially, however, the problem remains that the condition is: 'has one or several of the attributes/parts', however, I am looking for a condition that selects based one one threat having all the selected atttributes.

Cheers! 
Anna 

v-jiascu-msft
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hello, thank you so much for your solution.

 

I have done it step by step and I have downloaded your powerbi file and did the same. However, its not filtering based on the "values" slicer. Any idea why or how I can solve it?

 

Your help is very much appreicated.

 

Thanks,

In case if some one faces the same issue. Add the Measure to the table viz. 

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

Really helpful solution, thanks!

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,

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?  Data Tables.pngModel.pngPBI.png

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors