topic Re: ISBLANK doesn't work to replace null/blank values in a relationship between tables in DAX Commands and Tips

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

Anonymous — Mon, 25 Sep 2023 05:24:30 GMT

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 ?

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

Anonymous — Mon, 25 Sep 2023 05:44:29 GMT

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

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

Anonymous — Mon, 25 Sep 2023 05:47:13 GMT

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#)

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

Anonymous — Wed, 27 Sep 2023 07:43:20 GMT

Hi Anonymous ,

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.

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

Anonymous — Wed, 27 Sep 2023 23:28:15 GMT

HI Anonymous

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.