Forum Discussion
Handling on-going events [Beginner's Question]
Super UserYour DimTours and DimObservation tables are actually "fact" tables.
Fact tables contain values that can be treated mathematically (sum, avg etc). Dimension tables contain filter dimensions.
Having said that, Power BI doesn't really work like that. Facts and Dimensions are just an artificial construct to help us discuss data
Same with data models. You will hear Star schema, or Snowflake. While they are good guidance (and considered best practice by some) they are not strictly required.
Anyway, back to your data. You have multiple date fields in your fact tables. the Date table has hourly granularity. That is unusal, but possible. However, you currently have no way to link it to your observation table. Does the "time" column have a time or a datetime value? You need to find some composite key between the two tables, someting like YYYYmmDDhh, so you can link them etc.
For the Tours table you need to figure out which datetime field is "primary". Power BI only allows one active relationship between tables.
Hi, thank you for your answer and clarification! I have created this table with hourly granularity because initially I was thinking about linking everything based on hour and I've had star schema in mind. As for the observation table, I have one set of observations per hour:
But then, to connect my tables, I still need some bridge between them. Otherwise I won't be able to visualise for example average duration of the tour vs average temperature.
edit: Actually, I think I could use YYYYmmDDhh as a key, I just need to add a row to my tours table everytime a tour exceeds one hour(?) and then I have many to one relationship. Gonna try that!
So the only solution would be to create two relationships(one active and one inactive) to both date fields and then USERELATIONSHIP() to achieve expected output?
- lbendlin
that's one option, but (by far) not the oly one. You could also create a reference of your dates table so you can link the trip start to the dates table and the end time to the reference. Or you could use LookupValue etc.