Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I would need to calculate for each row in the table how many times the word "ÁNO", "NIE", ...
But the joke is that I need to count it not for the entire row but only for the selected columns in that row.
Thank you very much
Table:
Solved! Go to Solution.
Hi @mysho999 ,
I created a sample like this, and we’d like to calculate the counts of “ANO” & ”NIE” in selected columns for each row.
Attached pbix here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Ec6dsXHB20VMvdBdwazyAA...
We should have the slicer for selecting different columns, thus we do below steps in query editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTzV9KBkn6erlAyVicaRQyhCiSDLoYugywPMw1TBqbHEch2AmJnIHZRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Transposed Table" = Table.Transpose(#"Added Index"), #"Reversed Rows" = Table.ReverseRows(#"Transposed Table"), #"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"1", type text}, {"2", type text}, {"3", type text}, {"4", type text}, {"5", type text}, {"6", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"6"}, "Attribute", "Value") in #"Unpivoted Other Columns"
Please note that A,B,C,D are the columns name, and 1,2,3,4,5 are the Rows index
Applied the change, add the slicer of columns and rows, and use the measures:
Times of ANO = CALCULATE(COUNTROWS(Table2),FILTER(Table2,[Value]="ANO")) Times of NIE = CALCULATE(COUNTROWS(Table2),FILTER(Table2,[Value]="NIE"))
Best regards,
Dina Ye
Hi @mysho999 ,
If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!
Best regards,
Dina Ye
Hi @mysho999 ,
I created a sample like this, and we’d like to calculate the counts of “ANO” & ”NIE” in selected columns for each row.
Attached pbix here for your reference: https://wicren-my.sharepoint.com/:u:/g/personal/dinaye_wicren_onmicrosoft_com/Ec6dsXHB20VMvdBdwazyAA...
We should have the slicer for selecting different columns, thus we do below steps in query editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvTzV9KBkn6erlAyVicaRQyhCiSDLoYugywPMw1TBqbHEch2AmJnIHZRio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1), #"Transposed Table" = Table.Transpose(#"Added Index"), #"Reversed Rows" = Table.ReverseRows(#"Transposed Table"), #"Promoted Headers" = Table.PromoteHeaders(#"Reversed Rows", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"1", type text}, {"2", type text}, {"3", type text}, {"4", type text}, {"5", type text}, {"6", type text}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"6"}, "Attribute", "Value") in #"Unpivoted Other Columns"
Please note that A,B,C,D are the columns name, and 1,2,3,4,5 are the Rows index
Applied the change, add the slicer of columns and rows, and use the measures:
Times of ANO = CALCULATE(COUNTROWS(Table2),FILTER(Table2,[Value]="ANO")) Times of NIE = CALCULATE(COUNTROWS(Table2),FILTER(Table2,[Value]="NIE"))
Best regards,
Dina Ye
Hi @mysho999 ,
If my above post helps, could you please consider Accept it as the solution to help the other members find it more quickly. thanks!
Best regards,
Dina Ye
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |