Forum Discussion
Unable to create relationships between tables
Hello, I have two tables four tables I am trying to use and create relationships for.
1. Falls table - data table
2. Patient Days table - data table
3. Calendar table - lookup table
4. Location table - lookup table
I've tried creating relationships, but continue to get repetative data in my reports.
This is my first report in PowerBI and I'm really struggling to create these relationships. Here's a diagram of what I currently have.
Thanks for any help!
11 Replies
- AlexisOlson
Super User
I think the issue here is creating a many-to-many relationship between two fact tables as well as the ambiguity created by having multiple possible paths from Locations to Patient Falls (directly or through Patient Days).
I recommend reading this article to get a better idea what I mean:
https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/Thanks AlexisOlson! I was only able to use many:many because I had duplicate values both look-up tables. I cleaned them up and was able to revise my relationships. Below is what I have now, but I'm still getting repeating values! Any ideas on where to go from here?
Thank you!!
- AlexisOlson
It's not clear to me what you mean by "getting repeating values". Can you elaborate?
- KNP
Hi Anonymous,
If you have multiple fact tables at a different granularity, like this, it can certainly add complexity.
One possible solution (not always best practice, or most performant) is to merge the two fact tables.
Take the one that always has data (Patient Days if I'm understanding your data correctly) and merge >> left join Patient Falls in PowerQuery (transform data).
With one fact table joined to your dimensions it should simplify things.
As I said, this is one possible way to tackle the problem. I hope it helps.
Regards,
Kim
PaulDBrownCommunity Champion
I don't think that would solve the problem. It would Crossjoin fileds which are not common to both tables.
If I have one set of stores stores which sell groceries and another set of stores which sell Bicycles, I might have the common locations and dates, but there is no way I can try to relate type of bicycles to type of groceries unless I can relate them via customers (or patients in this case study). The only common fields in this case study are locations and dates. Not patients. So you can calculate data based on location and date. You can calculate how many type of bicycles and snacks where bought on a certain date and location, but you cannot calculate how many snacks where bought based on purchases of electric bicycles. To do that you need to know which customers bought both snacks and electric bicycles. In other words, you need a customer (or patient) dimension.Cross table calculations are limited to common dimensions.
So in this case study, you cannot count the number of falls (or "snacks") by "Units" (or "electric bicycles")- KNP
PaulDBrown - I see what you're saying. I don't know why, but I thought I saw, or I assumed that there was a patient ID field. That is the only way my suggestion would work.
Anonymous - is there a unique patient ID field of some kind that is not shown in the screen shots above?
Thanks PaulDBrown and AlexisOlson! You both helped me so much and both led me to the solution.
