Forum Discussion

garcia's avatar
garcia
Frequent Visitor
9 years ago
Solved

Measure to Consolidate Status for each RegNumber

Hi guys,
I have a simple table with three columns, see below.

I need to create a measure that generates as a result the consolidated STATUS by RegNumber as follows:

 

If the same RegNumber get at least one STATUS = PENDING, the end result should be PENDING for this REGNUMBER, otherwise STATUS = OK.

 

It´ll be show in a table visualization:

 

 

Thanks for all

Cheers

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi garcia,

     

    You can use below measure to check the status, then use selectcolumns and distinct function to get the result table.

    Sample:

     

     

    Measure:
    Check Status =
    var currentNumber= LASTNONBLANK(Sheet2[RegNumber],Sheet2[RegNumber])
    Return
    if(CONTAINS(FILTER(ALL(Sheet2),Sheet2[RegNumber]=currentNumber),Sheet2[Status],"Pending"),"Pending","OK")

     

    Table formula:
    Table = DISTINCT(SELECTCOLUMNS(Sheet2,"RegNumber",[RegNumber],"Status",[Check Status]))

     

     

    Result:


     

    Regards,
    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi garcia,

     

    You can use below measure to check the status, then use selectcolumns and distinct function to get the result table.

    Sample:

     

     

    Measure:
    Check Status =
    var currentNumber= LASTNONBLANK(Sheet2[RegNumber],Sheet2[RegNumber])
    Return
    if(CONTAINS(FILTER(ALL(Sheet2),Sheet2[RegNumber]=currentNumber),Sheet2[Status],"Pending"),"Pending","OK")

     

    Table formula:
    Table = DISTINCT(SELECTCOLUMNS(Sheet2,"RegNumber",[RegNumber],"Status",[Check Status]))

     

     

    Result:


     

    Regards,
    Xiaoxin Sheng

    • garcia's avatar
      garcia
      Frequent Visitor
      9 years ago

      Just PERFECT this solution!!! Thanks!!!!!