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

    • 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 ?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        I have just created a relationship between both Task ID columns and this is working perfect now.

         

        Thank you very much!