Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Check conditions in two columns based on a list

 Hello , 

I've found a number of posts that seem like they should help but I haven't been able to get this to work.

following is my problem:

 

               Column 1       Column 2        Resulting Column

Row 1     Names 1         Names 2        Check if 'column 1' contains a list of names, if yes then return that name, else check 'column 2' if  it contains the same list of names, if yes then return that name, else return "NA"

 

How do I go about this syntax where I have to check each cell if it contains multiple values referencing from a diferrent table( I can also add the names since they're not that many) ? 

 

Thanks in Advance

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    6 years ago

    Hi Anonymous ,

     

    Adding a calculated column as below.

    Column = 
VAR list =
    VALUES ( 'name list'[List] )
RETURN
    SWITCH (
        TRUE (),
        'Table'[Column1] IN list, 'Table'[Column1],
        'Table'[Column2] IN list, 'Table'[Column2],
        "not found"
    )

     

     

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Adding a calculated column as below.

    Column = 
VAR list =
    VALUES ( 'name list'[List] )
RETURN
    SWITCH (
        TRUE (),
        'Table'[Column1] IN list, 'Table'[Column1],
        'Table'[Column2] IN list, 'Table'[Column2],
        "not found"
    )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks a lot, it worked out perfectly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Update: It gave me the column values as I needed BUT when I made the relation between the table I ended up getting the following error "Circular dependency detected".

      I had to create three conditional columns in m query as a way around as apparently Power BI does not allow to have two calculated columns that contain measures that are also based on that table.