Not able to join 2 tables using relationships. Values are getting aggregated from 2nd table

[amritksahoo]

Hi Folks,

I am trying to join Actual hours per category and forecast hours per category from 2 different tables. However, I am unable to do so. I used all the permuations and combinations.

Actual Hours Table:

Service Category	Territory Name	Actual Hous	Date
ABC	US	5	1-Dec-2021
ABC	US	10	2-Dec-2021

Forecast Hours Table

Service Category	Territory Name	Forecast Hous	Date
ABC	US	10	1-Dec-2021
ABC	US	20	2-Dec-2021

Also in the forecast table there are future dates as well. I tried to build a realtionship using date, territory and service category. When I am trying to insert in one table, values from 2nd table is getting aggregated.

Results I am getting:

Service Category	Territory Name	Actual Hous	Forecast Hours	Date
ABC	US	5	30	1-Dec-2021
ABC	US	10	30	2-Dec-2021

Thanks in advance for the help.

[V-lianl-msft · Accepted solution]

Hi @amritksahoo ,

You can also try to create a new column:

Column = LOOKUPVALUE(Table2[Forecast Hous],Table2[Date],Table1[Date],Table2[Service Category],Table1[Service Category],Table2[Territory Name],Table1[Territory Name])

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@amritksahoo , You need to join both these tables with common dimensions like

Service Category, Territory Name and Date

and analyze them with help from common dimension

Common table

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos. star schema

https://docs.microsoft.com/en-us/power-bi/guidance/star-schema