Forum Discussion

BBurnett's avatar
BBurnett
Frequent Visitor
5 years ago
Solved

Conditional Column value based on seperate table

I would like to add a column and set its value based on whether or not a value exists in a different table. 

 

I have a table called Tasks which I want to add a column called WorkItemType. The value of this column will be a text entry that is defined by whether or not the parent of the row in tasks is in a different table.

 

So I have:

Tasks

WorkItemIdParentWorkItemId
123122
124135
129151


Bugs

WorkItemIdTitle
122A
128B

 

User Stories

WorkItemIdTitle
138Support01
135Feature01
151Feature02

 

And would like the result to be:

Tasks 

WorkItemIdParentWorkItemIdWorkItemType
123122Bug
124138Support
129151Other

 

The relationship structure looks like this:

 

 

In order to break it down into smaller chunks I have tried to get just the bug determination working. I have tried to following

 

= Table.AddColumn(Custom1, "WorkItemType", each if( Table.Contains (Bugs, Bugs[WorkItemId] = [ParentWorkItemId])) then "bug" else "other" )

 

But I get an error stating that:

 

Expression.Error: We cannot convert the value false to type Record.
Details:
Value=FALSE
Type=[Type]

 

I have also tried:

 

= Table.AddColumn(Custom1, "WorkItemType", each if( Table.Contains (Bugs, Bugs[WorkItemId] = Tasks[ParentWorkItemId])) then "bug" else "other" )

 

But that ends up with an error: Expression.Error: A cyclic reference was encountered during evaluation.

 

Any help would be greatly appreciated.

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi BBurnett ,

    You could try the following steps:

    Step 1,create a new table base on  User Stories:

    User Stories2 = CALCULATETABLE('User Stories',FILTER(all('User Stories'),'User Stories'[Title]="Support01"))

    Step 2， use the following measure :

    final =
VAR test1 =
    CONTAINS ( 'Bugs', Bugs[WorkItemId], MAX ( Tasks[ParentWorkItemId] ) )
VAR test2 =
    IF ( test1 = TRUE, "Bugs", "Others" )
VAR test3 =
    CONTAINS (
        'User Stories2',
        'User Stories2'[WorkItemId], MAX ( Tasks[ParentWorkItemId] )
    )
VAR test4 =
    IF ( test3 = TRUE, "Support", "Others" )
VAR test5 =
    IF ( TEST2 = "Bugs", "Bugs", IF ( test4 = "Support", "Support", "Others" ) )
RETURN
    test5

    Step3 ,new column base on the measure:

    final1 = [final]

     

    Wish it is helpful for you!

     

     Best Regards 

    Lucien

3 Replies

  • BBurnett's avatar
    BBurnett
    Frequent Visitor
    5 years ago

    Yes they are both the same data type. All integers. 

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    5 years ago

    Hi BBurnett ,

    You could try the following steps:

    Step 1,create a new table base on  User Stories:

    User Stories2 = CALCULATETABLE('User Stories',FILTER(all('User Stories'),'User Stories'[Title]="Support01"))

    Step 2， use the following measure :

    final =
VAR test1 =
    CONTAINS ( 'Bugs', Bugs[WorkItemId], MAX ( Tasks[ParentWorkItemId] ) )
VAR test2 =
    IF ( test1 = TRUE, "Bugs", "Others" )
VAR test3 =
    CONTAINS (
        'User Stories2',
        'User Stories2'[WorkItemId], MAX ( Tasks[ParentWorkItemId] )
    )
VAR test4 =
    IF ( test3 = TRUE, "Support", "Others" )
VAR test5 =
    IF ( TEST2 = "Bugs", "Bugs", IF ( test4 = "Support", "Support", "Others" ) )
RETURN
    test5

    Step3 ,new column base on the measure:

    final1 = [final]

     

    Wish it is helpful for you!

     

     Best Regards 

    Lucien