Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

19 REPLIES 19
Anonymous
Not applicable

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

Anonymous
Not applicable

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

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

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

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.

HI This is working for 1 slicer what I want to design 3 slicers to look in 6 columns. 

My data looks like Name A, Name B, City A , City B , State A , State B . I tried the above solution and Name in ( Name A , Name B ) slicer is working, But if I configure the same along with (City A, City B ), (State A, State B) its not working for me. Please advise.  

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

@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!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.