Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Identification Record Check

Hello   I have a table which consists of IDs that have two record types (blue or yellow). I want to create a calcualted column that does the following:   If ID has 'Blue' & 'Yellow' record state ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • johnt75's avatar
    johnt75
    4 years ago

    You can create a calculated table like

    Table 2 = 
var allBlue = CALCULATETABLE( VALUES('Table (2)'[ID]),'Table (2)'[Record] = "Blue")
var allYellow = CALCULATETABLE( VALUES('Table (2)'[ID]),'Table (2)'[Record] = "Yellow")
return UNION( 
    GENERATE( INTERSECT( allBlue, allYellow), { "Complete" }),
    GENERATE( EXCEPT( allBlue, allYellow), { "Blue" }),
    GENERATE( EXCEPT( allYellow, allBlue), { "Yellow" } )
)
johnt75's avatar
johnt75
Super User
4 years ago

You can create a calculated table like

Table 2 = 
var allBlue = CALCULATETABLE( VALUES('Table (2)'[ID]),'Table (2)'[Record] = "Blue")
var allYellow = CALCULATETABLE( VALUES('Table (2)'[ID]),'Table (2)'[Record] = "Yellow")
return UNION( 
    GENERATE( INTERSECT( allBlue, allYellow), { "Complete" }),
    GENERATE( EXCEPT( allBlue, allYellow), { "Blue" }),
    GENERATE( EXCEPT( allYellow, allBlue), { "Yellow" } )
)
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for your response. I have created a calculated table as suggested but instead of highlighting Task ID's that have either of the following:

 

Yellow & Blue record

Yellow only record

Blue only record

 

It has added a row in for each scenerio. In the below screenshot you can see that Task ID 718737 has a record of blue only but the calculated column has forced three rows with each scenario instead of highlighting 'Blue' only.

 

Any suggestions as to why this isnt working