Relationship with blank value

[ktt777]

I want to get a summary based on type of the items. And some items are blank will be calssified as "No data"

I want to use relationship in the example . I tried but blank data did not show up in the graph

Here is the link to the power bi file : https://1drv.ms/u/s!ApIDnMK2eKiFgRCgNLBdrSt3ue0q?e=dGzldn

Thanks, 

[MFelix · Accepted solution]

Hi @ktt777 ,

Your problem is that the relationship is not matching you data on one side you have blank and on the other you have 0.

What you can do is create a new column on the Cost table with the following code:

Relationship = IF(ISBLANK(Cost[Item]); "0";Cost[Item])

Now make the relationship based on this column.

Just some good practices advices

• Usage of many to many relationship is not the best option because of the way the data relates you can have results that are not the expected ones or confusing to the users
• Using blank values to make realtionship between tables is disadvice because the calculations will be hidden (has you have) or misscalculated so be carefull, relationships should be based on columns with values and not blanks.