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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

Building correct relationships between tables

Hi,

 

I am trying to build a matrix table with three columns:

Date | Sum of Transaction amount | Sum of ITF amount

 

The transaction amount is coming from Data table 1 (second image) and ITF amount is coming from Data table 2. This is my current model view.

 

aarcheco_1-1706680308228.png

 

However, when I put together a table, it seems that only the dates and transaction amounts from Data table 1 are being picked up. When I pull in the ITF amounts (From data table 2), they seem to just be a sum of all ITF amounts from data table 2. I've used Table 2 as an example to show how if i remove the 'transaction amount' field from Table 1, now the ITF amount and their respective dates pull through.

 

aarcheco_0-1706680195194.png

 

Ideall i want the table to consolidate both and show all dates (as seen in EITHER data table 1 and data table 2) and the respective transaction amount (from data table 1) OR ITF amount (from dat table 2). I.e. I expect each row to only have one cell in one (not both) of the columns filled with the individual (not sum) amount.

 

I feel like I have not built the correct relationship/s in my modelling which has caused this issue.

 

Thanks in advance,

Aaron

 

 

 

3 REPLIES 3
Idrissshatila
Super User
Super User

Hello @Anonymous ,

 

I advise you work your data model as a star schema data modeling 
check the concept as star schema https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Kishore_KVN
Super User
Super User

Hello @Anonymous , your model and data is completely wrong. You need to do many changes in the tables and the model. 

1. Calendar dimension table should not contain duplicates in dates. I can see there are duplicates as it is clearly visibile as many to many relationship. So remove duplicates in that. 

2. From Calendar table to Data Table1 create relationship on dates and then from Data Table1 to Data Table2 create relationship using other primary and foreign keys instead of dates. There should be one to many relationship only and keep the cross filtering as both direction always. 

3. Dont create any relationship between Data Table 2 and Calendar Table. 

 

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

Anonymous
Not applicable

Thanks for the reply.

 

I have done the following:

1. Date in Calendar contains no duplicates

2. Calendar --> Table 2 now linked with Date fields one-to-many relationship

3. Table 1 --> Table 2 now linked via Acccount ID and Medicare ID as key. Many-to-many relationship. Could not make it one-to-many relationship unfortunately.

 

aarcheco_0-1706765845249.png

 

Unfortunately resulting table is still the same as the original post.

 

I feel the issue is the relationship between Table 1 and Table 2. I don't think many-to-many is correct, but PowerBI won't allow me to select one-to-many...

 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors