PowerBI DAX, different contracts with same KPI title and Evaluators

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

Simple enough,

For fun only,