Forum Discussion
Helper IRelationship Model Issues - Cross Directional set as Both for unpivoted Tables
Hi All,
I'm working within a talent management database. Languages, Industry Experience, and Service Line Experience are all multi-select columns in source files. I unpivoted them in Power Query to be able to use individual selections as slicer filters for the end user to input numerous attributes and return table of matching talent. When I related the new tables back to primary they are many to one based on employee but I had to use Both as cross-filter direction in all cases so that the slicers created from the new individual attributes impacted the primary data set. Now, if I try to use fields from more than one of the unpivoted tables within the Power Automate field selections I get an error that Relationships for two or more fields can't be determined. So my assumption is it's saying that Language field isn't related directly to Industry Experience and the dual filter directionality causes it to error. Setting any of the relationships to Single direction will only allow the primary data (1 side) to filter the Unpivoted Tables (Many side); which breaks the slicer functionality and I haven't been able to switch that relationship directionality or test if that would even help in this case. Any solutions to meet all the requirements?
The error occurs because Power BI struggles to determine how the relationships should work when more than one unpivoted table is involved in the same visual.
Here are a few ways you can take to resolve this:
- Create a bridge table that contains unique “Employee ID” values. Relate all unpivoted tables and the primary table to this bridge table using a one-to-many relationship with single cross-filtering.
- You can create DAX measures to filter and calculate the data based on slicer selections. This approach can give you more control over how the data is filtered and displayed.
- Create calculated columns in your primary table that aggregate or summarize the data from the unpivoted tables. This can help in reducing the dependency on complex relationships.
You can also refer to this link:
Here are the similar cases for your reference.
Solved: Can't determine relationship between the fields wh... - Microsoft Fabric Community
Solved: Error: Can't determine relationship between fields - Microsoft Fabric Community
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
The error occurs because Power BI struggles to determine how the relationships should work when more than one unpivoted table is involved in the same visual.
Here are a few ways you can take to resolve this:
- Create a bridge table that contains unique “Employee ID” values. Relate all unpivoted tables and the primary table to this bridge table using a one-to-many relationship with single cross-filtering.
- You can create DAX measures to filter and calculate the data based on slicer selections. This approach can give you more control over how the data is filtered and displayed.
- Create calculated columns in your primary table that aggregate or summarize the data from the unpivoted tables. This can help in reducing the dependency on complex relationships.
You can also refer to this link:
Here are the similar cases for your reference.
Solved: Can't determine relationship between the fields wh... - Microsoft Fabric Community
Solved: Error: Can't determine relationship between fields - Microsoft Fabric Community
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.