DAX

[prashantg364]

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

[tamerj1]

@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"
    )
)

[Nathaniel_C · Accepted solution]

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

[Nathaniel_C]

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