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
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".
has this issue bee resolved
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.
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.
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
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.
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.
I do not understand your question.
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 | 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 |
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.
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.
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
Hi,
Share the download link of your PBI file and show the expected result with an explanation.
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:
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
98 | |
90 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |