Forum Discussion

dobrygom's avatar
dobrygom
Frequent Visitor
4 years ago

Missing data in visualizations

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

 

Need Help

5 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    4 years ago

    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's avatar
      dobrygom
      Frequent Visitor
      4 years ago

      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's avatar
        v-xiaotang
        Community Support
        4 years ago

        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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    4 years ago

    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.