Counting Occurances Across Multiple Columns With If Statements

[Anonymous]

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!

 

[Anonymous]

Hi @Anonymous ,

Could you please provide some raw data and explain more details on how to get the count of [FC Mode]? I'm not clear that why the count of FC Mode 2 and 5 is 1? 

Best Regards

[Anonymous]

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!

[Anonymous · Accepted solution]

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

[Greg_Deckler]

@Anonymous Should be able to get there using MC Aggregations or unpivoting your columns. Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community