topic Re: DAX in DAX Commands and Tips

DAX

prashantg364 — Sun, 02 Jul 2023 16:08:45 GMT

My data set is in a table as shown below. KPI1 to KPI 6 are calculated columns.

Now I want to calculate the total number of Pass KPI in a column at the end for the combination of Type and Band.

The expected output table is also shown below.

Input table

Area	Zone	Type	Band	KPI1(P/F)	KPI2(P/F)	KPI3(P/F)	KPI4(P/F)	KPI5(P/F)	KPI6(P/F)
BR	ARRH-0001	PRE	1	Pass	Pass	Fail	Pass	Pass	Fail
BR	ARRH-0001	PRE	2	Pass	Fail	Fail	Pass	Pass	Fail
BR	ARRH-0002	POST	2	Pass	Pass	Pass	Pass	Pass	Pass
BR	ARRH-0002	PRE	2	Pass	Fail	Pass	Pass	Pass	Fail
BR	ARRH-0002	PRE	1	Pass	Pass	Pass	Pass	Pass	Fail
BR	BGLP-0003	PRE	2	Pass	Fail	Pass	Pass	Pass	Fail
BR	BGLP-0003	PRE	1	Pass	Pass	Pass	Pass	Pass	Pass
BR	BGLP-0004	PRE	2	Pass	Pass	Pass	Pass	Pass	Fail
BR	BGLP-0004	PRE	1	Pass	Pass	Pass	Pass	Pass	Fail

Expected output table

Area	Zone	Type	Band	KPI1(P/F)	KPI2(P/F)	KPI3(P/F)	KPI4(P/F)	KPI5(P/F)	KPI6(P/F)	Pass KPI count
BR	ARRH-0001	PRE	1	Pass	Pass	Fail	Pass	Pass	Fail	4
BR	ARRH-0001	PRE	2	Pass	Fail	Fail	Pass	Pass	Fail	3
BR	ARRH-0002	POST	2	Pass	Pass	Pass	Pass	Pass	Pass	6
BR	ARRH-0002	PRE	2	Pass	Fail	Pass	Pass	Pass	Fail	4
BR	ARRH-0002	PRE	1	Pass	Pass	Pass	Pass	Pass	Fail	5
BR	BGLP-0003	PRE	2	Pass	Fail	Pass	Pass	Pass	Fail	4
BR	BGLP-0003	PRE	1	Pass	Pass	Pass	Pass	Pass	Pass	6
BR	BGLP-0004	PRE	2	Pass	Pass	Pass	Pass	Pass	Fail	5
BR	BGLP-0004	PRE	1	Pass	Pass	Pass	Pass	Pass	Fail	5

Re: DAX

Nathaniel_C — Sun, 02 Jul 2023 17:40:38 GMT

Hi prashantg364 ,
Please try this.

Count of Pass measure = var _KPI1 =MAX(PassTable[KPI1(P/F)]) var _KPI2=MAX(PassTable[KPI2(P/F)]) var _KPI3 =MAX(PassTable[KPI3(P/F)]) var _KPI4 =MAX(PassTable[KPI4(P/F)]) var _KPI5 =MAX(PassTable[KPI5(P/F)]) var _KPI6 =MAX(PassTable[KPI6(P/F)]) var _calc = IF(_KPI1= "Pass", 1,0) + IF(_KPI2= "Pass", 1,0) + IF(_KPI3= "Pass", 1,0) + IF(_KPI4= "Pass", 1,0) + IF(_KPI5= "Pass", 1,0) + IF(_KPI6= "Pass", 1,0) return _calc

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: DAX

Nathaniel_C — Sun, 02 Jul 2023 17:50:12 GMT

Hi prashantg364 ,
If You need a calculated column instead

Count of Pass col = IF([KPI1(P/F)]= "Pass", 1,0) + IF([KPI2(P/F)]= "Pass", 1,0) + IF([KPI3(P/F)] ="Pass", 1,0) + IF([KPI4(P/F)]= "Pass", 1,0) + IF([KPI5(P/F)]= "Pass", 1,0) + IF([KPI6(P/F)]="Pass", 1,0)

However, best use, most times, is using a measure.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Re: DAX

tamerj1 — Sun, 02 Jul 2023 18:07:12 GMT

prashantg364
Other approches

KPI count 1 = SUMX ( 'Table', INT ( 'Table'[KPI1(P/F)] = "Pass" ) + INT ( 'Table'[KPI2(P/F)] = "Pass" ) + INT ( 'Table'[KPI3(P/F)] = "Pass" ) + INT ( 'Table'[KPI4(P/F)] = "Pass" ) + INT ( 'Table'[KPI5(P/F)] = "Pass" ) + INT ( 'Table'[KPI6(P/F)] = "Pass" ) )

KPI count 2 = COUNTROWS ( FILTER ( SELECTCOLUMNS ( GENERATE ( GENERATESERIES ( 1, 6, 1 ), 'Table' ), "KPI", SWITCH ( [Value], 1, 'Table'[KPI1(P/F)], 2, 'Table'[KPI2(P/F)], 3, 'Table'[KPI3(P/F)], 4, 'Table'[KPI4(P/F)], 5, 'Table'[KPI5(P/F)], 6, 'Table'[KPI6(P/F)] ) ), [KPI] = "Pass" ) )