Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
zhengfu123
Frequent Visitor

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!

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

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:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

CNENFRNL
Community Champion
Community Champion

Simple enough,

CNENFRNL_0-1653717900574.png

 

For fun only,

CNENFRNL_1-1653718892378.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
CNENFRNL
Community Champion
Community Champion

Simple enough,

CNENFRNL_0-1653717900574.png

 

For fun only,

CNENFRNL_1-1653718892378.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

TomMartens
Super User
Super User

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:

image.png

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.