Forum Discussion
Slicer for multi cell value
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.
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.