Forum Discussion

zhengfu123's avatar
zhengfu123
Frequent Visitor
4 years ago
Solved

PowerBI DAX, different contracts with same KPI title and Evaluators

Hi all, currently i am struggle how to write a logic that will work for the following decision after few hours of brainstorming, here's the sample and the desired output:

Sample Data:

contract numberKPI numberEvalutorEvaluation status
Contract AKPI 1Evaluator Asubmitted
Contract AKPI 1Evaluator Bnot yet submitted
Contract AKPI 1Evaluator Cnot yet submitted
Contract AKPI 2Evaluator Anot yet submitted
Contract AKPI 2Evaluator Bsubmitted
Contract AKPI 2Evaluator Cnot yet submitted
Contract AKPI 3Evaluator Anot yet submitted
Contract AKPI 3Evaluator Bnot yet submitted
Contract AKPI 3Evaluator Csubmitted
Contract BKPI 1Evaluator Anot yet submitted
Contract BKPI 1Evaluator Bnot yet submitted
Contract BKPI 1Evaluator Cnot yet submitted
Contract BKPI 2Evaluator Anot yet submitted
Contract BKPI 2Evaluator Bsubmitted
Contract BKPI 2Evaluator Cnot yet submitted
Contract BKPI 3Evaluator Anot yet submitted
Contract BKPI 3Evaluator Bnot yet submitted
Contract BKPI 3Evaluator Csubmitted
Contract CKPI 1Evaluator Anot yet submitted
Contract CKPI 1Evaluator Bnot yet submitted
Contract CKPI 1Evaluator Cnot yet submitted
Contract CKPI 2Evaluator Anot yet submitted
Contract CKPI 2Evaluator Bnot yet submitted
Contract CKPI 2Evaluator Cnot yet submitted
Contract CKPI 3Evaluator Anot yet submitted
Contract CKPI 3Evaluator Bnot yet submitted
Contract CKPI 3Evaluator Cnot yet submitted
Contract DKPI 1Evaluator Asubmitted
Contract DKPI 1Evaluator Bnot yet submitted
Contract DKPI 1Evaluator Cnot yet submitted
Contract DKPI 2Evaluator Asubmitted
Contract DKPI 2Evaluator Bsubmitted
Contract DKPI 2Evaluator Cnot yet submitted
Contract DKPI 3Evaluator Asubmitted
Contract DKPI 3Evaluator Bnot yet submitted
Contract DKPI 3Evaluator Csubmitted

 

Desired Output:

Contract NumberContract Evaluation Status
Contract ACompleted
Contract BIncomplete
Contract CIncomplete
Contract DCompleted

so the question here is, different contract could have the same KPI title and evaluator, all of the KPIs tied to the same contract should at least be evaluated once regardless who evaluated it (Contract A scenario), if one of the KPIs has yet to be evaluated by any of the evaluator, the evaluation is incomplete (contract B scenario), contrat C is definately havent started any evaluation, contract D is similar to contract A but the KPI has been evaluated by more than one evaluator. Hope I can get some help, thanks!

Need Help
  • TomMartens's avatar
    TomMartens
    4 years ago

    Hey zhengfu123 ,

     

    it seems, that this measure returns what you are looking for:

     

    Measure = 
IF(
    MINX(
        ADDCOLUMNS(
            SUMMARIZE(
                'Table'
                ,'Table'[contract number]
                ,'Table'[KPI number]
            )
            , "NoOfEvaluations"
                , 
                    if( isblank( CALCULATE( COUNT('Table'[Evaluation status] ) , 'Table'[Evaluation status] = "submitted") )
                    , -1
                    , 1)
        )
        , [NoOfEvaluations]
    )
    = -1 , "incomplete", "complete"
)

    A table visual shows the expected result:

    The measure works like this,

    1. a virtual table is created based on the columns contract and kpi using summarize
    2. a column is added that holds the number of submitted evaluations per contract/kpi using ADDCOLUMNS. CALCULATE transforms the rowcontext contract/kpi into a filtercontext allowing to count the status where the status eq submitted. If the value returned by CALCULATE is blank, the column hold -1, otherwise 1.
    3. Using MINX( virtualtable , [NoOfEvaluations] ) inside an IF ...

    You might want to use an additional IF( HASONEVALUE( contract ) , ... , BLANK() ) to get rid of the value for the total line.

     

    Hopefully, this provides what you are looking for to tackle your challenge.

     

    Regards,

    Tom

4 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    4 years ago

    Hey zhengfu123 ,

     

    it seems, that this measure returns what you are looking for:

     

    Measure = 
IF(
    MINX(
        ADDCOLUMNS(
            SUMMARIZE(
                'Table'
                ,'Table'[contract number]
                ,'Table'[KPI number]
            )
            , "NoOfEvaluations"
                , 
                    if( isblank( CALCULATE( COUNT('Table'[Evaluation status] ) , 'Table'[Evaluation status] = "submitted") )
                    , -1
                    , 1)
        )
        , [NoOfEvaluations]
    )
    = -1 , "incomplete", "complete"
)

    A table visual shows the expected result:

    The measure works like this,

    1. a virtual table is created based on the columns contract and kpi using summarize
    2. a column is added that holds the number of submitted evaluations per contract/kpi using ADDCOLUMNS. CALCULATE transforms the rowcontext contract/kpi into a filtercontext allowing to count the status where the status eq submitted. If the value returned by CALCULATE is blank, the column hold -1, otherwise 1.
    3. Using MINX( virtualtable , [NoOfEvaluations] ) inside an IF ...

    You might want to use an additional IF( HASONEVALUE( contract ) , ... , BLANK() ) to get rid of the value for the total line.

     

    Hopefully, this provides what you are looking for to tackle your challenge.

     

    Regards,

    Tom

    • zhengfu123's avatar
      zhengfu123
      Frequent Visitor
      4 years ago

      hey man thank for you answer, it helps! just curious if i want to make it as a calculated column so I can count how many percentage of the contract has been configured?