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 "Complete"

If ID has only 'Blue' record state "Blue"

If ID has only 'Yellow' record state "Yellow"

 

Dataset Example

IDRecord
1Yellow
1Blue
2Yellow
3Yellow
3Blue
4Yellow
5Blue

 

Expected Outcome

IDCalculated Column
1Complete
2Yellow
3Complete
4Yellow
5Blue

 

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" } )
)

4 Replies

  • 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

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

        Can you post a shot of the calculated table from the Data view, and also any relationships you've created between the new table and the original. Also, which tables are the columns in your visual coming from ?