Dealing with Ambiguous Relationships and Multiple Granularity in Power BI

[hama21]

Hi everyone,

I'm currently facing a challenge in Power BI and would appreciate some advice from the community.

I have three tables in my dataset:

1. Table A: Contains monthly records of each client's transactions managed by an advisor.
2. Table B: Contains the monthly financial capture targets for each advisor.
3. Calendar Table: A standard date table used for creating relationships.

The relationships are currently set up through the Calendar Table. However, I cannot create direct relationships between Table A and Table B because it creates ambiguity due to the existing relationships through the Calendar Table.

Key Challenges:

• Table A has a higher granularity as it repeats advisors each month for every client they manage.
• Table B contains a single monthly target per advisor.
• The direct relationship between Table A and Table B on advisor and month fields is required for certain calculations but remains inactive due to the Calendar Table relationships.

I’ve tried several workarounds like concatenating fields to create unique keys, but they often feel like hacks and are not very efficient. I am looking for best practices or solutions that others have successfully implemented in similar scenarios.

Example of the Structure:

• Table A: Advisor, Client, Date, TransactionAmount
• Table B: Advisor, Date, CaptureTarget
• Calendar Table: Date, Year, Month, etc.

Current Relationships:

• Table A [Date] -> Calendar Table [Date]
• Table B [Date] -> Calendar Table [Date]

The problem arises because I need to compare the actual transactions from Table A with the targets from Table B, and the relationships through the Calendar Table do not suffice due to different granularities.

Question: How do you manage these types of relationships and calculations in Power BI without resorting to inefficient workarounds? Are there best practices for handling such scenarios where table granularities differ and direct relationships cause ambiguity?

Any advice or examples of how you've tackled similar issues would be greatly appreciated!

Thank you in advance!

[Fthrs_Analytics · Accepted solution]

I think you need an Advisor dimension in your data model to relate to Table A and Table B (like the date dimension does). I've attached an example .pbix file here.