Forum Discussion
DAX
I Have a table as shown below and I want to have a calculated column in the last to show the pass KPI count.
input table
| Area | KPI1 | KPI2 | KPI3 | KPI4 | KPI5 | KPI6 | KPI7 | KPI8 | KPI9 | KPI10 |
| sdc | pass | pass | pass | pass | pass | pass | fail | pass | pass | fail |
| sdf | pass | fail | fail | fail | pass | pass | pass | pass | pass | fail |
| gfr | pass | pass | fail | pass | pass | pass | pass | pass | pass | fail |
| ert | pass | pass | pass | pass | pass | pass | pass | pass | pass | pass |
| yui | pass | pass | fail | pass | pass | pass | pass | pass | fail | fail |
| mnh | pass | pass | pass | pass | fail | pass | pass | pass | fail | fail |
| gfd | pass | pass | pass | pass | fail | pass | pass | pass | pass | fail |
output table
| Area | KPI1 | KPI2 | KPI3 | KPI4 | KPI5 | KPI6 | KPI7 | KPI8 | KPI9 | KPI10 | Pass KPI count |
| sdc | pass | pass | pass | pass | pass | pass | fail | pass | pass | fail | 8 |
| sdf | pass | fail | fail | fail | pass | pass | pass | pass | pass | fail | 6 |
| gfr | pass | pass | fail | pass | pass | pass | pass | pass | pass | fail | 8 |
| ert | pass | pass | pass | pass | pass | pass | pass | pass | pass | pass | 10 |
| yui | pass | pass | fail | pass | pass | pass | pass | pass | fail | fail | 7 |
| mnh | pass | pass | pass | pass | fail | pass | pass | pass | fail | fail | 7 |
| gfd | pass | pass | pass | pass | fail | pass | pass | pass | pass | fail | 8 |
1 Reply
You should consider unpivoting your data into KPI and Result columns and just create a measure where the Result = "pass". That would be best practice. You could easily recreate the pivoted view in a matrix visual by putting the KPI column on columns in the matrix.
However, if you still want a calculated column, you could do it in the query editor with Record.ToList(_) and then counting the ones that are "pass" with List.Select. Or you could create a virtual table in a variable in DAX with {[KPI1], [KPI2], ... } and then use COUNTROWS and FILTER on that table where [Value] = "pass'.
Pat
