Unable to create relationships between tables

Anonymous · ‎09-30-2021

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!

Anonymous · ‎10-04-2021

Thanks @PaulDBrown and @AlexisOlson! You both helped me so much and both led me to the solution.

KNP · ‎10-01-2021

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.

Proud to be a Super User!

PaulDBrown · ‎10-01-2021

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")

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

KNP · ‎10-02-2021

@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?

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.

Proud to be a Super User!

AlexisOlson · ‎09-30-2021

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/

Anonymous · ‎10-01-2021

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 · ‎10-01-2021

It's not clear to me what you mean by "getting repeating values". Can you elaborate?

Anonymous · ‎10-01-2021

@AlexisOlson My # of Patient Days displays correctly, but the same # for patient falls repeats down the column when I try to link them together. The falls count displays correctly when I have it in a seperate table.

AlexisOlson · ‎10-01-2021

The first column, Units, only exists in the Patient Days table which does not filter the Patient Falls table. Hence there's no way to tell which falls are associated with which units and the measure sums over all of them.

The same holds true if you used Date from the Patient Days table instead of Date from the Calendar table.

PaulDBrown · ‎10-01-2021

If you don't have an "Units" column in the Patients Fall table you will get this. If you do have a Units column in both tables, you need to create a dimension table for Units too. In fact, you should create dimension tables for all fields common to both tables and use the dimension fields in visuals, slicers, measures, filters...

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

Anonymous · ‎10-01-2021

@PaulDBrown I'm confused.

Why would I ge this if I don't have an "issue" column in my patient's fall table? --- which I don't!

I simplified the fields in my tables, I don't have fields that are common to mulptiple tables except the location and date fields but this didn't resolve the issue.