Bridge table and visuals not working

[EWBWEBB]

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

https://we.tl/t-cTUVcS6lYX 

[EWBWEBB]

thank 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]

If this publication help , considering accept it as the solution to help other members find it more quickly.

[Syndicate_Admin · Accepted solution]

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]

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.