PowerBI DAX, different contracts with same KPI title and Evaluators

[zhengfu123]

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 number	KPI number	Evalutor	Evaluation status
Contract A	KPI 1	Evaluator A	submitted
Contract A	KPI 1	Evaluator B	not yet submitted
Contract A	KPI 1	Evaluator C	not yet submitted
Contract A	KPI 2	Evaluator A	not yet submitted
Contract A	KPI 2	Evaluator B	submitted
Contract A	KPI 2	Evaluator C	not yet submitted
Contract A	KPI 3	Evaluator A	not yet submitted
Contract A	KPI 3	Evaluator B	not yet submitted
Contract A	KPI 3	Evaluator C	submitted
Contract B	KPI 1	Evaluator A	not yet submitted
Contract B	KPI 1	Evaluator B	not yet submitted
Contract B	KPI 1	Evaluator C	not yet submitted
Contract B	KPI 2	Evaluator A	not yet submitted
Contract B	KPI 2	Evaluator B	submitted
Contract B	KPI 2	Evaluator C	not yet submitted
Contract B	KPI 3	Evaluator A	not yet submitted
Contract B	KPI 3	Evaluator B	not yet submitted
Contract B	KPI 3	Evaluator C	submitted
Contract C	KPI 1	Evaluator A	not yet submitted
Contract C	KPI 1	Evaluator B	not yet submitted
Contract C	KPI 1	Evaluator C	not yet submitted
Contract C	KPI 2	Evaluator A	not yet submitted
Contract C	KPI 2	Evaluator B	not yet submitted
Contract C	KPI 2	Evaluator C	not yet submitted
Contract C	KPI 3	Evaluator A	not yet submitted
Contract C	KPI 3	Evaluator B	not yet submitted
Contract C	KPI 3	Evaluator C	not yet submitted
Contract D	KPI 1	Evaluator A	submitted
Contract D	KPI 1	Evaluator B	not yet submitted
Contract D	KPI 1	Evaluator C	not yet submitted
Contract D	KPI 2	Evaluator A	submitted
Contract D	KPI 2	Evaluator B	submitted
Contract D	KPI 2	Evaluator C	not yet submitted
Contract D	KPI 3	Evaluator A	submitted
Contract D	KPI 3	Evaluator B	not yet submitted
Contract D	KPI 3	Evaluator C	submitted

Desired Output:

Contract Number	Contract Evaluation Status
Contract A	Completed
Contract B	Incomplete
Contract C	Incomplete
Contract D	Completed

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!

[TomMartens · Accepted solution]

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

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[CNENFRNL · Accepted solution]

Simple enough,

For fun only,

[TomMartens · Accepted solution]

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]

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?