Forum Discussion

vijenkin's avatar
vijenkin
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago
Solved

IF Table Column Contains Matching Value Place Another Value in New Column

Hello I am currently trying to solve a problem in PowerBi. I have two tables that have no relationship to each other. Table A contains a column with text that is user generated. Table B contains two columns that have a Unique Code and its corresponding Symbolic Name.

 

I want to "scan" each row of the column in Table A and IF a row contains the Unique Code from Table B, place the Unique Code and Symbolic Name from Table B into a brand new column in Table A. 

 

In short, I am trying to create a column that has the unique code and symbolic name information presented instead of the user generated information.

Example 

Data

 

Table A

Original Title
There is a problem with 012345
67890 spike over the last two weeks 
Many devices hitting 24680 as of last week

 

Table B

Unique CodeSymbolic Name
012345Network Error
67890Server Error
24680Client Error

 

Result

Table A

Original TitleNew Title
There is a problem with 012345012345 (Network Error)
67890 spike over the last two weeks 67890 (Server Error)
Many devices hitting 24680 as of last week24680 (Client Error)

 

Bonus Task

 

If the above can be solved then I will mark this problem as solved. However, it would be great if when the IF condition fails because no match is found the text in the Original Title column is returned

 

Original TitleNew Title
There is a problem with 012345012345 (Network Error)
67890 spike over the last two weeks 67890 (Server Error)
Many devices hitting 24680 as of last week24680 (Client Error)
Help needed for system problemHelp needed for system problem


I will not be able to share the PBIX file or provide screenshots of the actual data for privacy reasons

 

Any help or direction would be greatly appreciated. If this is not possible or more information is needed, please feel free to let me know. Thanks.

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    Hi,

    This calculated column formula works

    =if(ISBLANK(FIRSTNONBLANK(FILTER(VALUES(code_names[Unique Code]),SEARCH(code_names[Unique Code],Data[Original Title],1,0)),1)),Data[Original Title],FIRSTNONBLANK(FILTER(VALUES(code_names[Unique Code]),SEARCH(code_names[Unique Code],Data[Original Title],1,0)),1)&" ("&LOOKUPVALUE(code_names[Symbolic Name],code_names[Unique Code],FIRSTNONBLANK(FILTER(VALUES(code_names[Unique Code]),SEARCH(code_names[Unique Code],Data[Original Title],1,0)),1))&")")

    Hope this helps.

9 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    5 years ago

    Hi,

    This calculated column formula works

    =if(ISBLANK(FIRSTNONBLANK(FILTER(VALUES(code_names[Unique Code]),SEARCH(code_names[Unique Code],Data[Original Title],1,0)),1)),Data[Original Title],FIRSTNONBLANK(FILTER(VALUES(code_names[Unique Code]),SEARCH(code_names[Unique Code],Data[Original Title],1,0)),1)&" ("&LOOKUPVALUE(code_names[Symbolic Name],code_names[Unique Code],FIRSTNONBLANK(FILTER(VALUES(code_names[Unique Code]),SEARCH(code_names[Unique Code],Data[Original Title],1,0)),1))&")")

    Hope this helps.

    • vijenkin's avatar
      vijenkin
      Icon for Microsoft Employee rankMicrosoft Employee
      5 years ago

      Hi Ashish, I really appreciate the solution you provided. Unfortunately, when modifying the DAX to include the Data[Original Title] PowerBi does not recognize the column. For more context the table with the Original Title is being pulled in from Azure Dev Ops. Is there perhaps some kind of relationship or data type that needs to be had before the column can be recognized in DAX? 

       

      Thanks

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        5 years ago

        No relationship needs to be created.  I cannot say why it is not working on your file.