Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
hama21
Frequent Visitor

Dealing with Ambiguous Relationships and Multiple Granularity in Power BI

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!

1 ACCEPTED SOLUTION
Fthrs_Analytics
Frequent Visitor

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.

View solution in original post

1 REPLY 1
Fthrs_Analytics
Frequent Visitor

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.