I am not getting the expected results when 2 tables are combined

[manjeshjk]

Hi All,

I have 2 tables PLAN_WEEKLY

MaterialDatePlantQtyCWVersion

MANGO	17-Apr-24	8931	10	15_2024	1
MANGO	18-Apr-24	8931	20	15_2024	1
BANANA	17-Apr-24	8931	87	15_2024	1
BANANA	19-Apr-24	8931	22	15_2024	1

and Production_Actual

Prod_dateMaterialActual_QtyPlant

17-Apr-24	MANGO	34	8931
18-Apr-24	MANGO	100	8931
17-Apr-24	BANANA	12	8931
17-Apr-24	BANANA	89	8931
16-Apr-24	BANANA	70	8931
10-Apr-24	MANGO	44	8931
01-Apr-24	MANGO	54	8931
02-Apr-24	MANGO	50	8931
17-Apr-24	CHIKU	98	8931
01-Apr-24	CHIKU	55	8931

I have linked both material and Date from PLAN_WEEKLY to material and Prod_date in Production_Actual as show below.

My Expected Out is as below, however I am getting the summary of the material instead for for that date total.

I tried making relationship active both ways however was not successful.

Appreciate help from community to solve this issue.

Regards,

Manjesh

[Ashish_Mathur]

Hi,

Those 2 seem to be Fact Tables and therefore there should not a relationship between them.  Create 2 dim tables - Calendar and Material.  Create a relationship (Many to One and Single) from the 2 Fact tables to the 2 dim tables.

[manjeshjk]

Hello Ashish

I have done the same still I am getting the summary of the material as of that date

Is there a way to attach the pbix file.

Regards,

Manjesh

[Ashish_Mathur]

Hi,

In the second table, ensure that the material and date fields are from the Dim tables.

[manjeshjk]

Hello Ashish,

I did not understand your statement maybe you can specify the table names and may be a pciture would be helpful for me to understand.

Regards,

Manjesh

[Anonymous]

@Bmejia  @Ashish_Mathur  Thanks for your contribution on this thread.

Hi @manjeshjk ,

You can refer the following link to attach the pbix file in the community. Please exclude the sensitive info in the pbix file before share it. Thank you.

How to upload PBI in Community

Best Regards

[manjeshjk]

Hi,

I am working in a corporate environment and I cannot use google drive or dropbox. I have created a folder and moved the .PBIX file there, however when I cannot to share it any mail id outside the organizaiton. I tried to use my personal mail id to send the .PBIx file to. Appreicate more information on how to copy the file.

Regards,

Manjesh

[Bmejia]

Remember that in power BI you can only have one active connection and your active connection is the date connection.  The others connections won't work unless you activated in your measure or if your relationship has two relationship and don't want to activate the relationship everytime Then create a column where you merge the Material and date in each table then link the tables together the way

[manjeshjk]

Hello @Bmejia 

Can you demostrate with an example so that I can have a better understanding.

Regards,

Manjesh

[Bmejia]

The solid line is the only active relationship, the dotted line is there but not really doing anything until you call it out in a measure.  Base on the result that I got and matched your incorrect results. It seem your active relationship is based on the date only.

my original thought was that you create a column base on a key that is based on material and Date, (TableKey = Plan_Weekly[Material]&"-"&Plan_Weekly[Date]) it works as long as you are only using a date/Material from only one table but your data could be on either one. For example if I use the date from the production actual. It will miss 4/19/2024 data from the Plan weekly, because there is no product with the date, but it is in the plan weekly.

I think this is the way you can do it.

1st create a “New Table” or link the plan Weekly and Production Actual to a date table if you have one, if not use this would only give you the dates up to 2024 you could adjusted

Plan Weekly date = CALENDARAUTO(MONTH(12/31/2024))

2nd link the Plan Weekly on Plan Weekly Date on Date and Production Actual on Plan Weekly Date

3rd you can automate this table by creating a reference of the two tables when transforming the data remove the rest of the columns then append the value from one table to another then remove duplicates. I manually “enter data” with three material types.

4th link your two tables with material.

5th Create your visual table, Row/Material should be from you’re the Material table created, Column should be the date from the Plan Weekly date and your value just be from each table

[Bmejia]

[manjeshjk]

Hello @Bmejia ,

Thank you for the response. I have created 2 different tables material master and date Master and joined as mentioned in your reponse,however even now I am getting the desired results. I am attahcing the screen shot both the semantic model and the table for your reference. Let me know where have I gone wrong. Thank for being patient enough to answer my queries.

[Bmejia · Accepted solution]

The column date needs to come from the Date Master date.  

[manjeshjk]

Tnak you very much, now it is working as expected.