Forum Discussion
Counting Occurances Across Multiple Columns With If Statements
Hello,
I was wondering if I could get some helps with a problem.
I have a table labeled Original below. I would like to find the occurances of the FC modes by first find if the values of the Final FC (Final column) are in FC1, FC2, or FC3.
For example, in row A the Final is 10 which is in FC3 so count occurances in FC1, FC2, and FC3 (1,4, and 10).
In row B the Final is 0 which is all FCs so only count FC1 and ignore FC2 and FC3.
In row D the Final is 5 which is in FC2 so only count the occurances in FC1 and FC2 (4 and 5).
Thanks in advance!
Hi Anonymous ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Handle it in Power Query Editor first
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABFAbALhxOpEKzkBmQYoGCTqDGQZQTFC1AXIMoVqN4WLusJNRjIjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Final = _t, FC1 = _t, FC2 = _t, FC3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Final", Int64.Type}, {"FC1", Int64.Type}, {"FC2", Int64.Type}, {"FC3", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [FC1]=[Final] then Text.From( [FC1])&","&"," else if [FC2]=[Final] then Text.From( [FC1])&","&Text.From( [FC2])&"," else Text.From( [FC1])&","&Text.From( [FC2])&","&Text.From( [FC3])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Final", "FC1", "FC2", "FC3"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"FC1", "FC2", "FC3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FC1", Int64.Type}, {"FC2", Int64.Type}, {"FC3", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name"}, "Attribute", "FC Mode") in #"Unpivoted Columns"2. Create a matrix visual
Best Regards
4 Replies
Anonymous Should be able to get there using MC Aggregations or unpivoting your columns. Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community
Hi Anonymous,
The FC modes are the numbers that appear in the FC1, FC2, and/or FC3 columns. Starting at FC1, the counting stops if the FC mode value of the current row matches the value in the Final column of the same row.
For the two examples you circled:
- FC mode 2 appears once in row C. Since FC1= Final, we ignore FC2 and FC3 in row C.
- In row D, there are 1 occurance of 4 and 1 occurance of 5. Since FC2 = Final, we stop at FC2 and don't count FC3
I hope this makes sense. The data I provided is from the actual data but only includes a few entries.
Thanks!
Hi Anonymous ,
I created a sample pbix file(see the attachment), please check if that is what you want.
1. Handle it in Power Query Editor first
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0ABFAbALhxOpEKzkBmQYoGCTqDGQZQTFC1AXIMoVqN4WLusJNRjIjFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Final = _t, FC1 = _t, FC2 = _t, FC3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Final", Int64.Type}, {"FC1", Int64.Type}, {"FC2", Int64.Type}, {"FC3", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [FC1]=[Final] then Text.From( [FC1])&","&"," else if [FC2]=[Final] then Text.From( [FC1])&","&Text.From( [FC2])&"," else Text.From( [FC1])&","&Text.From( [FC2])&","&Text.From( [FC3])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Final", "FC1", "FC2", "FC3"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Custom", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"FC1", "FC2", "FC3"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"FC1", Int64.Type}, {"FC2", Int64.Type}, {"FC3", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Name"}, "Attribute", "FC Mode") in #"Unpivoted Columns"2. Create a matrix visual
Best Regards
