Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
randomUser21
Helper II
Helper II

how to manage joins on power bi

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 :

 

exemple - Power BI Desktop.jpgexemple - Power BI Deskt5op.jpg3.jpg6.jpg

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

 

1 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.

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

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

vjingzhang_0-1686794838387.png

 

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

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 ?

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.