Forum Discussion

garcia's avatar
garcia
Frequent Visitor
10 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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    10 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

Anonymous's avatar
Anonymous
Not applicable
10 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!!!!!