Counting across column for conditional values

[manojs]

Hello, I have a table with various columns to include: name, country1, country2, country3 and so forth

Each county1-countryX can have several values from a fixed defined choice. Example: Pass, Fail, N/A, etc

I want to produce a report for each name that counts the number of this values for each row across the columns

Below is an example, with columns CountPass & Count Fail countiing for each row across columns

Name, C1,C2,C3,C4, CountPass, CountFail

A,Pass,Pass, Fail, Fail, 2, 2

B Pass, Fail, Fail, Fail,1,3

C Fail,Fail,Fail,Fail, 0,4

Someone in the forum posted to simply add a coumn for each result

List.Count(List.Select(Record.FieldValues(_), (x) => Text.Contains(x, "PASS")))

List.Count(List.Select(Record.FieldValues(_), (x) => Text.Contains(x, "Fail")))

I have tried this but the column gives a value of "ERROR"

[ryan_mayu · Accepted solution]

@manojs 

you can try to use DAX to create columns, However, if the number of country is large , that will be complicated.

CountPass = 
VAR c1=if('Table'[C1]="Pass",1,0)
VAR c2=if('Table'[C2]="Pass",1,0)
VAR c3=if('Table'[C3]="Pass",1,0)
VAR c4=if('Table'[C4]="Pass",1,0)
RETURN c1+c2+c3+c4

CountFail = 
VAR c1=if('Table'[C1]="Fail",1,0)
VAR c2=if('Table'[C2]="Fail",1,0)
VAR c3=if('Table'[C3]="Fail",1,0)
VAR c4=if('Table'[C4]="Fail",1,0)
RETURN c1+c2+c3+c4

[parry2k · Accepted solution]

@manojs although I would recommend to unpivot your table but the code you posted is not working because when you add the second custom column, it sees the first custom column you added as a number because the type of that column by default will be any

There are two ways to handle it, either change type of column to text after your add first custom column and then add a second custom column or change expression as below 

List.Count(List.Select(Record.FieldValues(_),(x)=>Text.Contains(Text.From(x), "Fail"))))

Again, as a best practice, unpivot should be your approach.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[dax · Accepted solution]

Hi @manojs , 

You could refer to above suggestions, you also could refer to my M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQpILC5GUG6JmTkwKlYnWslJAbsckhJn7FIwFbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #" C1" = _t, C2 = _t, C3 = _t, C4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {" C1", type text}, {"C2", type text}, {"C3", type text}, {"C4", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Name", "Value"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Value]), "Value", "Count", List.Sum),
    #"Merged Queries" = Table.NestedJoin(#"Pivoted Column", {"Name"}, #"Changed Type", {"Name"}, "Pivoted Column", JoinKind.LeftOuter),
    #"Expanded Pivoted Column" = Table.ExpandTableColumn(#"Merged Queries", "Pivoted Column", {" C1", "C2", "C3", "C4"}, {" C1", "C2", "C3", "C4"})
in
    #"Expanded Pivoted Column"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@manojs 

you can try to use DAX to create columns, However, if the number of country is large , that will be complicated.

CountPass = 
VAR c1=if('Table'[C1]="Pass",1,0)
VAR c2=if('Table'[C2]="Pass",1,0)
VAR c3=if('Table'[C3]="Pass",1,0)
VAR c4=if('Table'[C4]="Pass",1,0)
RETURN c1+c2+c3+c4

CountFail = 
VAR c1=if('Table'[C1]="Fail",1,0)
VAR c2=if('Table'[C2]="Fail",1,0)
VAR c3=if('Table'[C3]="Fail",1,0)
VAR c4=if('Table'[C4]="Fail",1,0)
RETURN c1+c2+c3+c4

[manojs]

Thx Ryan,

This is good but would mean that it would rely on hard coding the column names, I was looking for an automated way and hence the two line examples in  my original post: though to get any information about that constructed that was posted is almost impossible on searching in google - looks like magic

[parry2k · Accepted solution]

@manojs although I would recommend to unpivot your table but the code you posted is not working because when you add the second custom column, it sees the first custom column you added as a number because the type of that column by default will be any

There are two ways to handle it, either change type of column to text after your add first custom column and then add a second custom column or change expression as below 

List.Count(List.Select(Record.FieldValues(_),(x)=>Text.Contains(Text.From(x), "Fail"))))

Again, as a best practice, unpivot should be your approach.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[AllisonKennedy]

What other data is in the table? What other columns do you have? I might suggest doing an unpivot of the Country columns in this case. If you have a fixed number of other columns, you can select them first and do an unpivot other columns. Once you have done the unpivot, all your pass/fail will be in 1 column called 'Value'. You could rename this to make more sense for you, and then simply plot that in a matrix or column visual, or do a simple measure:

Count Pass = COUNTROWS(FILTER(Table, Table[Value]="Pass"))

and plot that against country.

[parry2k]

@manojs I would recommend unpivoting your table, and then everything will be much easier, not sure if you are adding countpass and count fail columns, here are the steps

- transform data

- remove countpass and countfail columns (if these are part of the table)
- select name column 
- right-click, unpivot other columns it will add two columns, attribute, and value, rename these as per your requirement
- close and apply

To visualize,
- matrix visual:
- add name on rows,
- add attribute on columns
- add value on values section and the aggregation for this will be count, you can also create measures but let's starts with this. 

The above will get you the count you are looking for and this table transformation is following the best practice and can be easily used for other analyses.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡