Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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!
Solved! Go to 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,
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,
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! |
Hi,
You may download my PBI file from here.
Hope this helps.
Simple enough,
For fun only,
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! |
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,
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
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?
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
92 | |
89 | |
83 | |
77 | |
49 |
User | Count |
---|---|
146 | |
137 | |
109 | |
68 | |
55 |