Slicer On Multiple Values In One Column

[Budfudder]

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

[vishy0501]

has this issue bee resolved 

[Ashish_Mathur]

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.

[Anonymous]

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

[Ashish_Mathur]

Hi,

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

[Anonymous]

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: 

1	Matt Chirgwin
2	Sam Mackay, Marcelle Ahamefule, Alex Meyers, Cameron Hasley
3	Alex Meyers, Marcelle Ahamefule
4	Marcelle Ahamefule, Cameron Hensley
5	Matt Chirgwin
6	Matt 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

[Ashish_Mathur]

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.

[Anonymous]

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? 

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

Thank you. 

[Ashish_Mathur]

I do not understand your question.

[Anonymous]

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 1	column 2	column 3	column 4
100	active	google	Matt Chirgwin
200	active	business	Sam Mackay, Marcelle Ahamefule, Alex Meyers, Cameron Hasley
300	active	value	Alex Meyers, Marcelle Ahamefule
400	expired	adobe	Marcelle Ahamefule, Cameron Hensley
500	active	software	Matt Chirgwin
600	expired	hardware	Matt Chirgwin, Mandy Sandhu

[Ashish_Mathur]

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.  

[Anonymous]

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

[Ashish_Mathur]

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

[Anonymous]

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:

Number after the column was split by rows: 

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. 

Slicer looks good but affects visuals: 

Thank you, 

Christiana

[Ashish_Mathur]

Hi,

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

[Anonymous]

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.  

[Ashish_Mathur]

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

[Anonymous]

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. 

[Ashish_Mathur]

Sorry but i cannot share my e-mail address.

[Anonymous]

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. 

[Ashish_Mathur]

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