Changing mismatch 'Blank' with text

[hpilearn]

Hi, 

I want to create a visual that is using data from 3 related tables but encountered some mismatch issue. The sample tables look like this:

Table A

Item Number	Volume
a	10
a	5
b	11
null	7

Table B (no null value in this table)

Item Number	Category Number
a	001
b	002

Table C  (no null value in this table)

Catogory Number	Catogory Name
001	Grocery
002	Kitchen

As I want to create a pie chart presenting the volume distribution by item's Catogory Name, I got some 'blank' because there is 'null' value in the first table (as below).

Known I cannot combine the existing 3 tables into 1, and now I want to change the 'blank' in my pie chart to 'unknown'. Since the 'blank' here is not a value but a representation of mismatch. How can I achive this? Thanks!

My attempts:

1. change the null value in Table A to unknown -- fail

2. append a row with null value to both Table B and C, and try to link "null" with "null" -- still not working

[SpartaBI]

@hpilearn the blank appears when you have records in the many side of a relationship that doesn't have a matching key in the table at the one side. You can't replace it. You should always strive to have a model that each row in the many side has a represntative in the one side. Can you share a screenshot of the model view and write what columns play in the relationship.

[hpilearn]

Hi, thank you very much for the help! The relationship look like this:

There are null value in table A. Table B&C don't have any null value. 

[SpartaBI · Accepted solution]

@hpilearn you need to have something that will represent the values in table A also in table B (and in this case also table C cause they are in 1:1 so they actually should be 1 table). If you replace the null with unkonwn you also need to have the same unknown text in table B also with unknown text for category number and also in table C unkonwn text on the values of the new row you need to add.
Better to join table B and C before you bring it to the model, then you will need to do that only on one table.

[Hariharan_R]

Hi,

To achieve this in simple way, you can uncheck the blank on the visual filter.

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin

[hpilearn]

Hi Hari, thank you for your reply. I'm tyring to replace the "blank" with the text "unknown" but not to hide it. Do you have any suggestion on that?