I have a time table in my data model, but the issue is that I can’t create a relationship to the time restrictions I have in my fact table.
To put some context in to this, my fact table lists various restricted waiting times for parking - some of which are over multiple days for different locations. I have attempted to split out these times by day and then then used logic reasoning (yes/no) to give me something very crude in Power BI.
Is it possible to write DAX to show time alongside these multiple restrictions or perhaps set up my fact table in a different way.
From what I can see in your Data Model, you will need to create an Index Column on all tables of concern. You did create a Time Key on the Time Table, however, you need a corresponding Index on the other Table to the many side. Further, your model is Shared via SharePoint so myself or others can’t authentic on the Power Query to demonstrate. There are lots of videos on creating indices in the DNA portal and YouTube:
Re-read through your first post and clearly define exactly what you want to achieve. I recommended taking screen shots and annotate using 3D paint the columns and DAX you want to develop exactly.
Here is an example post…from a problem I had a few days back.
Just my 2 cents but your times_of_enforcement table is 58 columns wide and it’s structure isn’t great for analysis, if you ask me. From a quick overview it seems there are a couple of things to take into account: Months, Days and Times, so a structure like this seems more suited.
Starttime and Endtime keys can be linked to the Time dimension table but that’s not required, you could also do that virtually. As for Month and Day I would recommend creating a virtual relationship with a Date dimension table (oddly enough that seems to be missing from your model entirely…)
Here’s code for a Date table to get you started.
And I fully agree with @adam before you proceed further take some time to clearly define exactly what you want to achieve.
The times_of_enforecement are unique values that correspond to a fact table, I have already published. The difficulty with the restrictions are that they can apply to the same road and day at multiple times or even different times.
A date dimension table is not necessary here since these restrictions apply all year, whatever the month. It’s just day that matters.
I have uploaded a sample data for you to look at. In green we have the various restrictions (which could apply to more than one road) then in yellow you have my attempt of trying to extrapolate this data. Further still, in blue, you have a table that says whether the time on a particular day is true/false.
What I need to be able to collate are the time restrictions (both start and end) across the days of the week.
What I am wondering is, whether my data set is in the best format.
Okay final attempt because this is still to little to go on to be honest but since I always wanted to do a Gantt Chart - here we go.
I’ve created a simple model based on your times_of_enforecement that I reshaped. Note that I added a Period to ID multiple time frames across the same day.
