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

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.  

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

      So I figured out what the problem was. I created the column in the wrong table. I forget for certain DAX functions the corresponding value has to be in the same table. Now with that said I don't get any errors from the DAX but when activating the column all of the rows are 

       

      0 (Success)

       

      I am assuming this means the function is working but can't find anything. I checked the code_names[Unique Code] tablle and the codes that are in Data[Original Title] are indeed there.

       

      Any thoughts? 

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

        There has to be some spelling error or some extra spaces/invisible characters.