Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Calculated column related tables

Hey all,

 

I have a table called WorkItems History that looks like so:

 

 

And a table called statuses that looks like so:

 

There is currently no relationship between these tables. 

 

I want to create 2 new columns in WorkItems History that will tell me the StatusCategory an item has moved From and To 

 

For example, if for a particular row it was moving From 'Open' To 'In Progress' then I would expect a new column called 'StatusCategoryFrom' with a value of 'To Do' and 'StatusCategoryTo' with a value of 'In Progress' 

 

Note: different IssueTypes may have different statuses and/or statuscategories...

 

Help me please 🙂 ❤️ 

Need Help

2 Replies

  • dadb25's avatar
    dadb25
    Helper I
    2 years ago

    Hi Anonymous ,

     

    To make a relationship between these 2 tables, it must be a "one to many" relationship.

    It means, you must have unique values in your column : statuses[Status].

    You can do it in Power Query, by apllying remove duplicates.

     

     

    Then you can make your relationship.

     

    Hope it helps 😁

     

    Did I answer your question? Mark my post as a solution!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    You can create relationship between the 2 tables. Drag the key column from the 'statuses' table to the corresponding foreign key column in the 'WorkItems History' table to create a relationship. 

     

    Then create 2 columns.

    StatusCategoryFrom = 
LOOKUPVALUE('statuses'[StatusCategory], 'statuses'[StatusID], 'WorkItems History'[PreviousStatusID])

    StatusCategoryTo = 
LOOKUPVALUE('statuses'[StatusCategory], 'statuses'[StatusID], 'WorkItems History'[CurrentStatusID])

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.