Forum Discussion

prashantg364's avatar
prashantg364
Helper 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.

The expected output table is also shown below.

 

Input table

AreaZoneTypeBandKPI1(P/F)KPI2(P/F)KPI3(P/F)KPI4(P/F)KPI5(P/F)KPI6(P/F)
BRARRH-0001PRE1PassPassFailPassPassFail
BRARRH-0001PRE2PassFailFailPassPassFail
BRARRH-0002POST2PassPassPassPassPassPass
BRARRH-0002PRE2PassFailPassPassPassFail
BRARRH-0002PRE1PassPassPassPassPassFail
BRBGLP-0003PRE2PassFailPassPassPassFail
BRBGLP-0003PRE1PassPassPassPassPassPass
BRBGLP-0004PRE2PassPassPassPassPassFail
BRBGLP-0004PRE1PassPassPassPassPassFail

 

Expected output table

AreaZoneTypeBandKPI1(P/F)KPI2(P/F)KPI3(P/F)KPI4(P/F)KPI5(P/F)KPI6(P/F)Pass KPI count
BRARRH-0001PRE1PassPassFailPassPassFail4
BRARRH-0001PRE2PassFailFailPassPassFail3
BRARRH-0002POST2PassPassPassPassPassPass6
BRARRH-0002PRE2PassFailPassPassPassFail4
BRARRH-0002PRE1PassPassPassPassPassFail5
BRBGLP-0003PRE2PassFailPassPassPassFail4
BRBGLP-0003PRE1PassPassPassPassPassPass6
BRBGLP-0004PRE2PassPassPassPassPassFail5
BRBGLP-0004PRE1PassPassPassPassPassFail5



  • 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



3 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    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



    • Nathaniel_C's avatar
      Nathaniel_C
      Community Champion
      3 years ago

      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

  • tamerj1's avatar
    tamerj1
    Community 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"
    )
)