Cycle through status records

[Anonymous]

Hi Guys, 

ive gotthis data set.

the rules are to be for each Scenario ID, which has multiple Steps and statuses,

I need to set the Overall status based on the statuses for all the steps for each Scenario ID

Scenario ID            Step Number      Status

IOJ-0001		1	Planned
IOJ-0001		2	Completed
IOJ-0001		3	Comleted
IOJ-0001		4	N/A
IOJ-0001		5	N/A
IOJ-0001	Livia	6	Planned
IOA-0001		1	Error
IOA-0001		2	In Progress
IOA-0001		3	N/A
IOA-0001		4	Error
IOA-0001		5	N/A
IOA-0001		6	Blocked
IOA-0001		7	Retest
IOA-0001		8	N/A

So for IOJ-0001 

the rules 

If all Steps are Completed, then the Overall status is set to Complete

if any steps are in error, the overall steps are ERROR

if All steps are in progress overall steps in progress

[az38 · Accepted solution]

Hi @Anonymous 

try a measure like

Measure = 
var _isCompleted = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Scenario ID]),'Table'[Status]="Completed")
var _isError = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Scenario ID]),'Table'[Status]="Error")
var _isInProgress = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Scenario ID]),'Table'[Status]="In Progress")
var _rowsByID = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Scenario ID]))
RETURN
SWITCH(TRUE(),
_isCompleted = _rowsByID, "Completed",
_isError > 0, "Error",
_isInProgress = _rowsByID, "In Progress",
BLANK()
)