DAX

[prashantg364]

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

[ppm1]

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