Forum Discussion

prashantg364's avatar
prashantg364
Icon for Helper II rankHelper II
3 years ago
Solved

DAX

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. ...
  • Nathaniel_C's avatar
    Nathaniel_C
    3 years ago

    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



tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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