Forum Discussion
Helper IIIBridge table and visuals not working
I have a model where I need to a use a bridge table to join together a dimensions table and a fact table.
The dimensions table contains all my people information, so a single person may have multiple PersonKey's but only one PersonId.
The Fact table only has the PersonId. So I use a bridge table to join the two together.
The bridge table is a distinct list of PersonId.
When using the name for example from DimPerson in visual A and the details from the fact table in visual B.
Visual A can filter Visual B without issue and does so correctly.
However when I try to use the name from DimPerson and the details from the fact table in the same visual I get an error saying 'can't determin the relationship'.
I'm sure this is an issue with the relationship but I can't think how else to do this.
DimPerson * - 1 Bri_PersonId (Both)
Bri_PersonId 1 - * Fact_Attendance (Single)
Files here which demonstrate the issue with a small ammount of Dummy data
Analyze your data well and you will be able to realize that the relationship PersonKey doesn't exist in your Fact_Attendance table and when you decompose your DimPerson, you just keep keeping the PersonId relationship.
Another alternative is to make a many-to-many relationship (not a good practice)I would recommend that you manipulate the logic of your PersonKey and insert the date period as a conditional column, for example 12345a (to: February2024), and then concatenate with PersonID to have a unique element.
I hope it helped your problem
4 Replies
Syndicate_AdminAdministrator
Hello
You would first have to evaluate whether it is necessary to include the PersonKey column, because you do not have that detail in your Fact_Attendance table.
Possible actions you can analyze:- If you need PersonKey for your analysis, include it in your Fact_Attendance table. and you perform a key by joining ID.PersonNumber = CONCATENATE(DimPerson[PersonId], DimPerson[PersonKey]) to have unique values.
- If PersonKey is just for informational value, create a new table with PersonId and PersonKey, handle it as a fact table, and insert it as a detail sheet to use as tooltips.
- If you need PersonKey for your analysis, include it in your Fact_Attendance table. and you perform a key by joining ID.PersonNumber = CONCATENATE(DimPerson[PersonId], DimPerson[PersonKey]) to have unique values.
EWBWEBBthank you.
The PersonKey in DimPerson is used to filter another fact table so I can't change that.
I can't get get the personkey into the Fact_Attendance table as a single personId can have multiple PersonKeys with a PersonKey typically covering a date range.
I guess I'm trying to fudge this a little bit using a DimPerson table from a different database (A) to filter a fact table from another database (b) that is slightly different in that the PersonId is unique in database (b) but not in database (a).
- Syndicate_Admin
Analyze your data well and you will be able to realize that the relationship PersonKey doesn't exist in your Fact_Attendance table and when you decompose your DimPerson, you just keep keeping the PersonId relationship.
Another alternative is to make a many-to-many relationship (not a good practice)I would recommend that you manipulate the logic of your PersonKey and insert the date period as a conditional column, for example 12345a (to: February2024), and then concatenate with PersonID to have a unique element.
I hope it helped your problem
- Syndicate_Admin
If this publication help , considering accept it as the solution to help other members find it more quickly.