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 valu...
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

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