The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
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.
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
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
Proud to be a 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!!
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.
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...