Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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.
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.
Thanks a lot Yolo Zhu.
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.
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"
Proud to be a Super User! | |