how to manage joins on power bi

[randomUser21]

Hello everybody,

I have a command table and an objective table,

there is a sales target for each month and each item category

in my matrix I display my categories with the number of orders, I would like to display the objective planned for the category near the number of orders.

I have two problem:
- it shows me the total objective
- and it does not show me the categories with 0 orders yet it has an objective

for example :

I do not have the objective associated with the category, and I do not have a line for category phone 0 order with its intended objective

someone can help me please

[v-jingzhang]

Hi @randomUser21 

Just use Objective table's category column on Rows of the matrix. And create the following measure

Number of id_order = COUNT('Order'[id_order]) + 0

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

[randomUser21]

thank you @v-jingzhang ,
in my real case I can't because I made the join on the id of the category and I don't have the name of the category in the objective table, I must add the name of the category  in category table ?

[v-jingzhang · Accepted solution]

Hi @randomUser21 

You can add a Dimension Category table which has category id and category name columns. Then connect this table to both Objective and Order tables on the category id column. Remove the current relationship between Objective and Order tables.

Add a dimension Date table to the model, connect it to order_date in Order table. Add a YearMonth column in the Date table, and connect it to YearMonth column in Objective table (you have to create this column in Objective table in advance).

Then use Date table's date column in the slicer. This will modify the relationships between tables to follow the star schema concept: Power BI Basics of Modeling: Star Schema and How to Build it - RADACAD. The purpose is that the dimension tables (Category and Date) can filter the fact tables (Order and Objective). And dimension tables have full and distinct values so you won't miss data. 

In addition, if you don't want to modify the relationships or add new tables to the current model, adding the name of the category to Objective table will be the easiest solution. 

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