Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Cycle through status records

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

Need Help
  • az38's avatar
    az38
    6 years ago

    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()
)

1 Reply

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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()
)