Forum Discussion
Not able to join 2 tables using relationships. Values are getting aggregated from 2nd table
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.
Hi Anonymous ,
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.
2 Replies
Anonymous , 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 schemahttps://docs.microsoft.com/en-us/power-bi/guidance/star-schema
Hi Anonymous ,
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.
