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. ...
  • 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



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