Relationship or LEFT JOIN, populate field from table if result is null or blank

[Anonymous]

Hi

Not sure how best to ask this question but here goes.  

I am trying to populate a field on my visual with a value from 'PersonName' column if the field or column 'CorrectName' is blank.  This happens because the table 'ProjectsCombined' does not have all the "ID_no's" so rather than have a blank field I wanted to fallback to table 'ProjectsDetails' and select the field called 'PersonName' instead.  How does one go about this, and shoudl I be looking at creating this as a measure or do I need consider a calculated column on the 'ProjectsDetails' table;  whcih will test the relationship between the two tables and if  blank fallback to default or main table 'ProjectsDetails' column 'PersonName'.  Below are a couple of drawings to illustrate what i am trying to do, the text in red is to show I want to use the PersonName value instead.  I should also say that the column 'PersonName' will be removed from the visual, for now it was helping me to identify which row(s) are empty or blank.

I am doing this in PowerBI DeskTop.

RelationShip 1-ManyMy Visual 

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

Please try to use NATURALINNERJOIN() function create a calculated table and then create a calculated column.

Table = NATURALINNERJOIN(ProjectsDetails,ALLEXCEPT(ProjectsCombined,ProjectsCombined[ID]))

Column = IF(ISBLANK('Table'[CorrectName]),'Table'[PersonName],'Table'[CorrectName])

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Jay

Thanks, thats works nicely for me.

Chris