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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors