Join 2 different tables without removing unique codes

[Mohamadmonem]

Hello everyone!

I have 2 different tables.

One of them is related to planned production.

Another is actual production.

I would like to join 2 tables. The similar column is product code. However, when I make a model between them, the power BI visualizes just those product codes which have produced.

the report must visualize all of codes even those they have not produced.

For example, in table 1(planned production), we have 3 codes. 101 , 102, 103

And in table 2(actual production), we have 102,103, 104

In my report, power bi just visualize 102, 103

How to solve that?

Thanks in advance

[v-jingzhang · Accepted solution]

Hi @Mohamadmonem 

One recommended practice is to add a Dim product table to the model. You can create a Dim table with below DAX simply. It will have all distinct product values from planned and actual tables. This can avoid missing products. Then link the Dim table (one-side) to another two tables (many-side) on Product columns. 

Product = DISTINCT(UNION(DISTINCT('actual production'[Product]),DISTINCT('planned production'[Product])))

When you create a visual, use Dim table's Product column in it. 

In addition, Power BI displays non-blank data in visuals by default. So if your data is missing due to that it has blank values, you can right click on Product column in the field and check the "Show items with no data" option in the context menu. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[AlexisOlson]

It sounds like you did some kind of inner join on the two tables rather than appending them.

Even if you do append them, it would still be useful to have a product dimension table.