cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Counting across column for conditional values

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"

 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Counting across column for conditional values

@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

1.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Highlighted
Super User VII
Super User VII

Re: Counting across column for conditional values

@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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Community Support
Community Support

Re: Counting across column for conditional values

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.

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: Counting across column for conditional values

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.

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
Super User VII
Super User VII

Re: Counting across column for conditional values

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User III
Super User III

Re: Counting across column for conditional values

@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

1.PNG

Did I answer your question? Mark my post as a solution.Appreciate your Kudos!
Proud a to be a Datanaut!
Thanks and BR
Ryan

View solution in original post

Frequent Visitor

Re: Counting across column for conditional values

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

Highlighted
Super User VII
Super User VII

Re: Counting across column for conditional values

@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.

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Community Support
Community Support

Re: Counting across column for conditional values

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.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors