ISBLANK doesn't work to replace null/blank values in a relationship between tables

[LD2022]

I have a common dimension i.e. Account joined to Fact 1 and Fact 2.

The requirement is to show Fact1 and Fact2 metrics by Account.Account # based on Fact1.Account ID = Account.Account ID and Fact2.Account Id =Account.Account ID. 

However, there are few accounts in Fact1 and Fact2 that doesn't have a matching Account ID in Account table. I would like to replace the Account # with "Unknown" if it is blank but

IF(ISBLANK(Account.Account#),"Unknown",Account.Account#)

and 

IF(Account.Account#="","Unknown",Account.Account#) doesn;t seem to work. 

Could you please help ?

[LD2022]

HI @v-rzhou-msft 

Appreciate your response. However, the 2 fact tables are huge and combining them would be resource intensive especially when the Account # column is used in slicers -I am using Account Dimension to join the Fact tables to avoid the performance issues otherwise I could have done a left outer join in Power Query to handle this issue.

[Abhinav054]

I think It didn't work because you used "and" operator, try chnging it to "OR ( || )"

[LD2022]

What I meant by the below is none of these conditions work, I tried them separately and together with OR operator but it doesn't work. 

IF(ISBLANK(Account.Account#),"Unknown",Account.Account#)

and 

IF(Account.Account#="","Unknown",Account.Account#) 

[v-rzhou-msft]

Hi @LD2022 ,

I think measure or calculated colunn are not supported in your situation. I suggest you to create a calculated table to achieve your goal.

My Sample:

Calculated table:

Table = 
ADDCOLUMNS(UNION(Fact1,Fact2),"New Account ID",IF([Account ID] in VALUES(Account[Account ID]),[Account ID],"Unknown"))

Result is as below.

Best Regards,

Rico Zhou

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