Forum Discussion
Slicer for multi cell value
I have columns which is kind of following cell value at different cells.
r1,r2,r3
r1,r2
r1
r2
r2,r3,r4
i was trying to create slicer selection of unique value from above column likes of r1 r2 r3
Hi Vishwamber
You can just modified the measure
Measure = IF(COUNTROWS(FILTER(VALUES('Query1 (2)'[Value]),CONTAINSSTRING(SELECTEDVALUE(Query1[plat]),[Value])))>0,CALCULATE(COUNTA(Query1[TC])),0)Then create a new measure and put the measure to the table visual filter and put the new measure to the pie chart
Measure1 = SUMX(Query1,[Measure])Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Are you saying that "r1, r2, r3" are all contained in one field?
In Power Query, do a Split Column by Delimiter, and under Advanced, select "Rows"
Then Remove Duplicates to get to only ONE row of "r1"
Hi Vishwamber
Do you describe the sample data as shown in the figure below.
If the sample data is like the picture above, you can put the following code to Advanced Editor in power query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjJU0lEqMgIRxkqxOsgCCC6cY4TOAWoCEiZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns"), #"Removed Blank Rows" = Table.SelectRows(#"Removed Duplicates", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))) in #"Removed Blank Rows"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for this however I need to use same for later filter for eg if I select r1 it will filter all value for row which contain r1.
TC Status plat tc123 pass r1,r2 tc124 fail r1,r3,r5 tc125 fail r1,r4,r3,r7 tc126 pass r2,r5,r2,r3 tc127 fail r1,r6 tc128 fail r5 tc128 fail r1 If I choose r1 it will filter all
TC Status plat tc123 pass r1,r2 tc124 fail r1,r3,r5 tc125 fail r1,r4,r3,r7 tc127 fail r1,r6 tc128 fail r1 Hi Vishwamber
You can refer to the following solution.
Step 1: You can create two blank query, then put the following code to advanced editor in power query then apply them.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkk2NDJW0lEqSCwuBlJFhjpFRkqxOhAJE6BIWmJmDlTCWKfIFC5niipnApY2h0ubIZlpBNSnAyKN4dLmqLrN4BIWSBKmWEUNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TC = _t, Status = _t, plat = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"TC", type text}, {"Status", type text}, {"plat", type text}}) in #"Changed Type"let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkk2NDJW0lEqSCwuBlJFhjpFRkqxOhAJE6BIWmJmDlTCWKfIFC5niipnApY2h0ubIZlpBNSnAyKN4dLmqLrN4BIWSBKmWEUNlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TC = _t, Status = _t, plat = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"TC", type text}, {"Status", type text}, {"plat", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "plat", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"plat.1", "plat.2", "plat.3", "plat.4"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"plat.1", type text}, {"plat.2", type text}, {"plat.3", type text}, {"plat.4", type text}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"TC", "Status"}, "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"TC", "Status", "Attribute"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns") in #"Removed Duplicates"Step 2: put the value of query(2) to slicer.
then create a measure
Measure = IF(COUNTROWS(FILTER(VALUES('Query1 (2)'[Value]),CONTAINSSTRING(SELECTEDVALUE(Query1[plat]),[Value])))>0,1,0)Then put the measure to the table visual filter
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
On the selection of r1 my pi chart only will show the satus of all r1. Need to link same.
Hi Vishwamber
You can just modified the measure
Measure = IF(COUNTROWS(FILTER(VALUES('Query1 (2)'[Value]),CONTAINSSTRING(SELECTEDVALUE(Query1[plat]),[Value])))>0,CALCULATE(COUNTA(Query1[TC])),0)Then create a new measure and put the measure to the table visual filter and put the new measure to the pie chart
Measure1 = SUMX(Query1,[Measure])Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
