Forum Discussion
Common join column error question when joining between Power BI many-to-many relationship tables.
We are going to attempt a JOIN between the two tables below in Power BI.
The goal of calculation is to be able to check by time zone in the view table as the time zone appropriate for each type through JOIN between the two tables.
'DIM_SCHEDULE' table example
[TYPE] [START] [END] [Status] → (Work status)
Type1 | 09:00 | 10:30 |True
Type1 | 10:30 | 10:40 | False
Type1 | 10:40 | 12:00 | True
Type1 | 12:00 | 13:00 | False
Type1 | 13:00 | 15:00 | True
Type2 | 09:00 | 10:30 |True
Type2 | 10:30 | 10:40 | False
Type2 | 10:40 | 12:00 | True
Type2 | 12:00 | 13:00 | False
Type2 | 13:00 | 15:00 | False
Type3 | 00:00 | 00:00 | False
'VIEW_SOMEONE' table example
[ProdNum] [Date] [WType] [INPUT] [OUTPUT]
Product_1 | 23-01-02 | Type1 | (DateTime) | (DateTime)
Product_1 | 23-01-03 | Type1 | (DateTime) | (DateTime)
Product_2 | 23-01-03 | Type1 | (DateTime) | (DateTime)
Product_2 | 23-01-04 | Type1 | (DateTime) | (DateTime)
Product_3 | 23-01-03 | Type2 | (DateTime) | (DateTime)
Product_3 | 23-01-04 | Type1 | (DateTime) | (DateTime)
Product_3 | 23-01-01 | Type2 | (DateTime) | (DateTime)
There are two tables like above.
DIM's Type and VIEW's WType are the same text. The two tables are connected in a many-to-many relationship with each type. After the matching rows, I would like to increase the rows and columns of data for each time zone of the DIM corresponding to each type.
However, both the NATURALLEFTOUTERJOIN and NATURALINNERJOIN functions do not work. I get an error saying a common join column is required.
This does not work whether the table names of the two connected columns are the same or not. What is the cause of this problem and is there a solution?
Sounds like you are attempting to do the join in DAX. For that you need to use CROSSJOIN and then filter down to the matching types.
Would be better to do this in Power Query, it supports full outer joins etc.
1 Reply
- lbendlinSuper User
Sounds like you are attempting to do the join in DAX. For that you need to use CROSSJOIN and then filter down to the matching types.
Would be better to do this in Power Query, it supports full outer joins etc.