Once Slicer for multiple columns

[Anonymous]

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

Threat	Node	Activity	Part One	Part Two	Part Three	Part Four	Part Five
Area One	Down	Name 1		Base Management		Production Shop	Production Shop
Area One	Down	Name 2	Base Management	Production Shop	Flow Assurance	Production Shop	Production Shop
Area One	Up	Name 3		Production Shop	Base Management	Production Shop	Flow Assurance
Area One	Up	Name 4		Production Shop	Process Engineer	Area Ops	Production Shop
Area One	Up	Name 5		Flow Assurance	Production Shop	Area Ops	Flow Assurance
Area One	Left	Name 6		Production Shop	Flow Assurance	Production Chemist	Production Shop
Area One	Left	Name 7		Production Shop	Flow Assurance	Process Engineer	Production Shop
Road	Right	Name 8		Base Management	Production Shop	Base Management	Corrosion
Road	Right	Name 9	Base Management	Gateway	Flow Assurance	Corrosion	Corrosion
Upper Deck	Right	Name 10		Base Management	Production Shop	Base Management	Flow Assurance
Upper Deck	Right	Name 11	Base Management	Production Chemist	Process Engineer	Area Ops	Flow Assurance

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

Threat	Node	Activity	Part One	Part Two	Part Three	Part Four	Part Five
Area One	Down	Name 1		Base Management		Production Shop	Production Shop
Area One	Up	Name 3		Production Shop	Base Management	Production Shop	Flow Assurance
Road	Right	Name 8		Base Management	Production Shop	Base Management	Corrosion
Upper Deck	Right	Name 10		Base Management	Production Shop	Base Management	Flow Assurance

Thanks

[Anonymous]

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! 

[msraymch]

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...

[Anonymous]

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]

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.

[Anonymous]

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]

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]

Hello Dale,

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

It should look like this;

Threat	Node	Activity	Part One	Part Two	Part Three	Part Four	Part Five
Area One	Down	Name 1		Base Management		Production Shop	Production Shop
Area One	Down	Name 2	Base Management	Production Shop	Flow Assurance	Production Shop	Production Shop
Area One	Up	Name 3		Production Shop	Base Management	Production Shop	Flow Assurance
Road	Right	Name 8		Base Management	Production Shop	Base Management	Corrosion
Road	Right	Name 9	Base Management	Gateway	Flow Assurance	Corrosion	Corrosion
Upper Deck	Right	Name 10		Base Management	Production Shop	Base Management	Flow Assurance
Upper Deck	Right	Name 11	Base Management	Production Chemist	Process Engineer	Area Ops	Flow Assurance

[Anonymous]

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

[v-jiascu-msft · 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 ()
)

Best Regards,

Dale

[pbijess857]

This is so helpful. I hope you have a great day - you deserve it. 

[akhilduvvuru]

@v-jiascu-msft - This slicer is impacting the other slicers. If I apply any other filter from filter pane, the table is not filtering. Can you help me with the same please? Thanks!

[sanjanarama]

I tried same solution for matrix and it is not working, can anyone help out

[Anonymous]

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,

[Anonymous]

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

[Anonymous]

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

[Debbie]

Really helpful solution, thanks!

[selpaqm]

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,

[Anonymous]

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?