Forum Discussion
Follow on query - Relationship issues between dates in two databases
Hi all,
This post is a follow-up from a previous query I posted on this forum - https://community.fabric.microsoft.com/t5/Desktop/Date-filter-applying-to-one-table-but-not-another/m-p/3511820#M1160504
The outcome of my previous query was that "the model is incorrect and/or the relationships that have been created are incorrect (many-to-many, filtering both ways)". In an attempt to get to the bottom of this issue I have recreated a simplified version of the dashboard with minimal relationships, but I am still having the same issue.
In short, there are two databases I want to combine into a table in PowerBI. One on project bookings, and another on project expenses. The final table should sum up all project bookings and expenses to provide a total overall project spend. I would like to be able to filter this data by date, to see how project spend has changed over time. Following some advice from another forum post I have produced a Calendar Date table which contains every individual date from the start of the project until the end of the project which also has the same data type and format. The idea is to use this as a link between Bookings [Date] and Expenses [Date] which have some but not all date values.
For the simplified version, I have three tables. 'Bookings', 'Expenses', and 'Calendar Date'. Each of the three tables has a column called 'Date' which shows when the booking/expense occured. I have linked the 'Bookings' and 'Expenses' tables to the Calendar Table using single direction filters (see below).
As suggested by HotChilli in his response to my previous query, I have tried to create a table which shows the date from 'Calendar Date', the date from 'Bookings' and the date from 'Expenses', but it will only let me add in the 'Bookings' date OR the 'Expenses' date (see example of successful table below). If I try to add them both then I get an error - "Can't display the data because PowerBI can't determine the relationship between two or more fields".
If I can get these three fields to link successfully then it will help significantly with other elements of my dashboard, but I am completely stuck.
Any advice would be appreciated!
1 Reply
"the date from 'Calendar Date', the date from 'Bookings' and the date from 'Expenses'",,... "If I try to add them both then I get an error" - one of them will have to be an aggregation. This issue pops up all over the web but I've never seen it before, I suppose because I wouldn't do this in practice. I think you're seeing it as a result of your relationship testing.
I think I was careful in my original response to the original problem to say use 2 visuals and a slicer to test the relationships.
So, as long as the 2 relationships are working, what you are seeing is normal behaviour and you can move to the next stage.
