Missing data in visualizations

[dobrygom]

There are 2 sample tables visible in the screenshot:  Table1 with T1Key and T1Value and Table2 with T1Key and T2Value columns.

Left side/pie chart represents joining within the 'Model' on *Key columns (keys C and D exist in both tables).

Right side/pie chart represents both tables merged (MT = merged tables) using "Full Outer" join

Values A and B are missing from the left pie chart because there are no corresponding T2 values for these T1 keys.

The only way I found to overcome this issue was to merge both tables (right chart pie) but I don't think this is the best method.

Q1. how to make sure that all data is displayed (using relationships in 'Model' method) ?

       Note: when "Show items with no data" is enabled for the left pie chart, keys A and B do show up in the visualization

                 legend but there are no corresponding slices for them.

Q2. how to show count of total rows after joining both tables (8) ?

[Ashish_Mathur]

Hi,

In the Query Editor, append the 2 tables.  This will create a 3 column table - T1 Key, T1 value and T2 value.  Now build your visuals/write measures.

[sturlaws]

Hi,

I would suggest to create a dimension with the unique keys from both tables:

TableKeys = DISTINCT(union(VALUES(TableA[Key]),VALUES(TableB[Key])))

and create relationship the keys of your two tables. Then create this measure:

Measure = sum(TableA[ValueA])+sum(TableB[ValueB])

Use [Key] from the new table as category on your pie chart

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[dobrygom]

I created the following table:   T1+T2 Keys = DISTINCT(union(VALUES('Table 1'[T1Key]),VALUES('Table 2'[T2Key])))  and joined it to Table 1 and Table 2 (chart below).

Because corresponding T2Values are null for T1+T2 Keys:  A and B, they are missing from the chart.

The desired effect is as visible in the MT (merged tables) chart.

I wonder if there is a way for PBI to recognize null values as blank values and not cut them out from the visuals.

[v-xiaotang]

Hi @dobrygom 

The reason is that in the returned table, the value of row A/B is null instead of "". you need to create a column

result

Best Regards,

Community Support Team _Tang

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

[dobrygom]

desired outcome is shown below (note: columns E,F,G,H in the chart pie menu are (Blank) )

the only way I found to achieve it (workaround) was by merging tables (full join)

the join seemed to have replaced null values with blanks

this is likely not the best workaround because if more than 2 tables are used, the joined "all tables in one" table would be massive and hard to manage

what would be a proper/lighweight way to achieve this ? (basically not loosing key column values if related tables' values are null)?