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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Budfudder
Helper IV
Helper IV

Slicer On Multiple Values In One Column

I have a table that contains a number of columns, one of which is a description. Here's a sample of the values in the description column for various rows:

BLUE METAL SQUARE
RED METAL SQUARE
ORANGE CIRCLE
YELLOW WOOD
BLACK TRIANGLE GLASS
GREEN DIAMOND
GREEN WOOD SQUARE
RED PLASTIC
RED CIRCLE

I want a slicer that contains all of the possible attributes above, so it looks like this:

BLACK
BLUE
CIRCLE
DIAMOND
GLASS
GREEN
METAL
ORANGE
PLASTIC
RED
SQUARE
TRIANGLE
WOOD
YELLOW

The idea is that if nothing is selected in the slicer, then the visual shows all rows in the table. But if anything is selected in the slicer, then the visual shows only those rows in which that attribute is present. So, if the user selected "SQUARE", the only rows shown would be those with the following values:

BLUE METAL SQUARE
RED METAL SQUARE
GREEN WOOD SQUARE

The user might then also select "METAL", in which case "GREEN WOOD SQUARE" would drop off the list, as it doesn't contain both selections.

 

I can't figure out how to do this - how to, in effect, have a slicer that says "If a particular field contains the selection, show it. If it doesn't, hide it".

27 REPLIES 27
vishy0501
Helper I
Helper I

has this issue bee resolved 

Ashish_Mathur
Super User
Super User

Hi,

 

One approach would be to Split the Description column by rows so that each word of each cell appears in a row of its own.  Then we create another Table of unique entries from this Description column.  We then create a relationship from the base data table to the table which has the unique entries.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur

 

I am facing the same issue as the this thread. Can you please explain further what you mean by your approach? I would like to test this out for my data. 

 

Thank you. 

 

Christiana

Hi,

Share some data, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur

 

I would like one slicer to display the following list of values: 

 

Matt Chirgwin

Sam Mackay
Alex Meyers

Marcelle Ahamefule

Cameron Hensley

Mandy Sandhu

 

From the coloumn below: 

 

1Matt Chirgwin
2Sam Mackay, Marcelle Ahamefule, Alex Meyers, Cameron Hasley
3Alex Meyers, Marcelle Ahamefule
4Marcelle Ahamefule, Cameron Hensley
5Matt Chirgwin
6Matt Chirgwin, Mandy Sandhu

 

The point is to draw the name once into the slicer which captures every time the name appears in the column i.e Marcelle Ahamefule will capture rows 2, 3 and 4, Matt Chirgwin will capture rows 1, 5 and 6. At the same time Cameron Hensley will capture rows 2 and 4. The same concept should apply to all the values. The list I am working with is a very long one with some rows with 4 to 5 names. The steps provided earlier only works for the first name on the list - I have already played around with this concept.

 

Ideally, this should work as new names are added. Automatically.  

 

Thank you. 

 

Christiana

Hi,

In the Query Editor, reference the original table and remove all columns other than the Name column.  Split the column by delimiter >Comma and under Advanced Options, select Rows.  Remove Duplicates.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur

 

Thank you. This will work if I can apply the same with multiple columns. I mean the index numbers need to match the names in the rows. When I split them by row the names lose th relationship with the column/rows. Is there a way? 

 

1Matt Chirgwin
2Sam Mackay, Marcelle Ahamefule, Alex Meyers, Cameron Hasley
3Alex Meyers, Marcelle Ahamefule
4Marcelle Ahamefule, Cameron Hensley
5Matt Chirgwin
6Matt Chirgwin, Mandy Sandhu

 

Thank you. 

I do not understand your question.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur 

 

The coloumn that the slicer is based on is related to other coloumns. When splitting column 4 below in to rows it loses the relationship with the respective row. Please see below example of the data. Does this clear it up? 

 

column 1column 2column 3column 4
100activegoogleMatt Chirgwin
200activebusinessSam Mackay, Marcelle Ahamefule, Alex Meyers, Cameron Hasley
300activevalueAlex Meyers, Marcelle Ahamefule
400expiredadobeMarcelle Ahamefule, Cameron Hensley
500activesoftwareMatt Chirgwin
600expiredhardwareMatt Chirgwin, Mandy Sandhu

What do you mean by "loses the relationship with the respective row"?  Based on the table shared in your previous post, show the exact result that you are expecting.  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur

 

Sorry. I see what you are saying. The row just duplicates.

A few questions-

Do you know if this affects other visuals? i.e. having a duplicate row may be captured and make the data inaccurate. 

Will this column automically split when new data is entered? 

 

Thank you, 

Christiana

 

 

The result will refresh when new data is entered.  Build your visuals and post back if you face any problems.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur

 

I have built my visuals based on this method in a test file. It seems the data has duplicated if the name appears twice or more - I thought this may happen. Please see below screenshots. 

 

Do you think there is a way to do this without the data duplicating? 

 

Correct numbers:

christianadaa_1-1661839673204.png

 

 

Number after the column was split by rows: 

 

christianadaa_0-1661839652633.png

 

Row split by comma > rows:

As you can see the first entry is duplicated 3 x - my visuals have captured this 3 x which is inaccurate. 

christianadaa_4-1661839839710.png

 

 

 

Slicer looks good but affects visuals: 

christianadaa_3-1661839762267.png

 

Thank you, 

Christiana

 

Hi,

Share the download link of your PBI file and show the expected result with an explanation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur

 

Below is the link to the test file: 

https://drive.google.com/file/d/12f5pTnnIVfyffYj-Xp3Z5w4GWe16n0lN/view?usp=sharing

 

I have copied and pasted the column that was split by delimeter > comma > rows (column4) which has duplicated the rows where there was more then one name in column4. The issue is the these duplicate rows have been captured in my visuals which is inaccurate. 

 

Ideally, I would like:

  • the sme slicer function to capture duplicate names in column4 only once. 
  • the visuals to capture duplicate rows only once-that means columns 1-3 that have duplicate data should only appear in the visuals once so the value is not inaccurate. This is because there are multiple people assigned to one project. So the multiple people (column4) should appear once in the slicer however the project should only appear once in the visuals otherwise the data is inaccurate. 

 

I hope that makes sense. 

Please let me know if you need more info. 

 

Thank you.  

Access Denied message.  In that same file, show the expected result as well.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi @Ashish_Mathur,

 

May I have your email? 

 

The expected result is what I can not figure out - to capture projects only once in the visuals so there are no duplicates. 

 

Thank you. 

Sorry but i cannot share my e-mail address.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

I understand. There is no sensitive data and the link should work now. 

 

https://drive.google.com/file/d/12f5pTnnIVfyffYj-Xp3Z5w4GWe16n0lN/view?usp=sharing

 

Your time and help is greatly appreciated. 

Thank you. 

I do not understand anything in your file at all.  What result do you want to see?  Give a proper explanation.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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