I have 2 tables, encounters table and procedures table, that both have 2 columns containing a date data type and 2 columns containing a time data type. I have created a Calendar lookup and a Time lookup table to connect with the aforementioned tables to create a relationship in my data model and build a heatmap with each "60 Min Slot" as columns. I can't figure out a way to get around the many-to-many relationship that happens when I try it the way I thought would work. Data imported from csv files.
Here is how I've designed the data model so far. I can't figure out how to model the data so that each 60 Min Slot in the Time lookup corresponds to each Start.Time/Stop.Time without running into the many-to-many issue. Is there a better way to achieve what I'm trying to do? (https://i.sstatic.net/f5cQFzW6.png)(https://i.sstatic.net/pzV8EqTf.png)
Similar to your calendar lookup, ensure your time lookup has a Time column and use this in your relationship, not the 60 Min Slot column.
Take care to ensure your Time relationship is of the same grain (ie to the second or to the minute).
