Forum Discussion
One column, two filters in the same chart
Hello,
Let me try to explain what I want to achieve.
I have a table similar to that:
| ID | Labels |
| a | e;f |
| b | f;e |
| c | e;x |
| d | y;x |
I have a Labels column with multiple labels separated by a semicolon. With power query I can split this column to 2 columns or 2 rows with the semi colon delimeter. In my opinion I should split the labels into 2 rows to achieve what I want but let me know if I'm wrong.
My objective is to create a stacked bar chart with a legend and an axis based on the same column, however, the filter need to be different for the axis and the legend.
Let's say, I want the legend to filter on labels x, y, z and the axis filter on labels : a, b, c.
In other words, I want a stacked bar chart that will show the interesection of 2 labels, the one in the legend and the one in the axis. How can I have 2 different filters based on the same column? Power BI only let me change the filter for both axis and legend in the same time.
Let me quickly explain why I think splitting the Labels column in 2 columns does not work for me:
Even if 2 different columns allow me to apply different filters on each of the column, if the labels is not in the correct order, my charts will miss some data (if I filter on x in Labels.1 column but there are x in Labels.2 column, then I miss all the x in column 2).
I hope it's clear, I'm pretty sure there is a smart way to do what I want with Power BI but I need some guidance.
Thank you!
4 Replies
- amitchandak
Super User
RemyW , I got only part of it. If you need two filter on same column, you need create one onf them using an independent table
Need of an Independent Table in Power BI: https://youtu.be/lOEW-YUrAbE
Compare Categorical Data Using Slicers - Compare two Brands: https://youtu.be/exN4nTewgbc
Hi amitchandak
Thank you very much for your response. Let me rephrase my issue in a more straightforward way.
Here is my table:
Labels Label A, Label B Label B, Label A Label C, Label D Label D, Label C Label E, Label F When I split this column by delimeter (comma) here is what I get (which makes sense):
Labels.1 Labels.2 Label A Label B Label B Label A Label C Label D Label D Label C Label E Label F What I'm trying to achieve is to sort the labels in the right column, for example I want to get all the labels A, C, and E in Labels.1 column and the other in the Labels.2 column. I don't know how to sort the labels in the way I want. Typically, based on the table above, this output:
Labels.1 Labels.2 Label A Label B Label A Label B Label C Label D Label C Label D Label E Label F Does it make sense? Is that a complex question?
Note: It's not possible to have twice the same label in the same row.
Thank you,
Rémy
- v-angzheng-msft
Community Support
Hi, RemyW
From what I've got, what you want to achieve is to split some labels to the left column and some other labels to the right column.
Try this:
M query:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8klMSs1RcNRRgDCclGJ1YIJOMEFHJEFnmKALkqALTNAZSdAVJuimFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Labels = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Labels", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Labels", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Labels.1", "Labels.2"}), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Labels.1", Text.Trim, type text}, {"Labels.2", Text.Trim, type text}}), #"Added Custom" = Table.AddColumn(#"Trimmed Text", "left", each List.Min({[Labels.1],[Labels.2]})), #"Added Custom1" = Table.AddColumn(#"Added Custom", "right", each List.Max({[Labels.1],[Labels.2]})) in #"Added Custom1"Note that it is important to use Trim to remove the extra spaces.
Result:Please refer to the attachment below for details.
Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi v-angzheng-msft ,
Thank you very much for your proposed solution, it looks very promising. The only thing I'm not sure to understand is how do you choose precisely what label is in what column.
I can see the function list.min and list.max but is it fully customizable? Obviously, my table is a lot more complex and contains a lot more different labels. If I decide to get Label D in column "left" instead of right as in your example. How will I achieve that?
Thanks again!
Rémy,
