The problem I’m facing is that in one fact table there are two dates:
So, in power bi with “userelationship”(1) function I can choose whenever I want to use the start-date or the end-date in measures because when these dates are both joined to the date column in the calendar table they get deactivated and you have to activate the relations through the function(1).
But, in looker I have no idea how to replicate this behavior, because if the start date is joined to the calendar view, then ‘Startdate’ will be used for ALL the metrics.
And if I join both startdate/enddate I think that then it will create a join that meets startdate=enddate=calendardate. Which is unwanted.
I’m thinking of creating a new view with the fact table or a new calendar view, but it doesn’t seem to be user friendly.
Perhaps this could be a use case for templated filters and liquid parameters. Here’s a little info on it: https://cloud.google.com/looker/docs/templated-filters
What I’m thinking is that you set up a templated filter in the view. The user selects which date they want to join on, start or end. Then based on the selection for this filter, you can use a liquid parameter which refers back to the value selected for the filter, and essentially have a dynamic join that way. Let me know if that makes sense.