Forum Discussion
Working with 2 time dimensions
My data comes from a CSV, not a pristine star schema.
I have requests that get opened then closed.
The data gives the opening and closing date for each RequestID.
REQUESTID | OPEN_DATE | CLOSE_DATE
I want to show on a time line the number of both opened and closed requests on a given date.
I created a time dimension that includes all the dates (basically from MIN (OPEN_DATE) to MAX (CLOSE_DATE) ).
I obviously can’t create a n-to-1 relationship to both OPEN_DATE and CLOSE_DATE on the same table.
Is the trick to create an alias for the table? Simply referencing the existing table in powerquery
How does that impact performance?
More importantly how do we then apply common filters on visualizations where measures from both tables are shown side by side? Do we have to create separate lookup tables for every dimension we want to filter on?
You can use the USERELATIONSHIP function when you need to use the inactive relationship in your measures.
Here's a good explanation: https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
7 Replies
You can use the USERELATIONSHIP function when you need to use the inactive relationship in your measures.
Here's a good explanation: https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
The text of the above link is correct but I think the link itself contains a few extra characters causing it to give a 404 error
Try this instead
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
Also take a look here
http://exceleratorbi.com.au/many-many-relationships-dax-explained/
I'm flabergasted by the simplicity (yet power) of the USERELATIONSHIP function .
This is exactly what I was looking for.
Thank you so much MalS for pointing to it
It kinda work but I can’t quite explain the behaviour
The active relationship is on the close_date, so looking at date or close date is the same thing
And when I want to count Requests by close date, I don’t need to do anything special (besides adding a special filter that is specific to my project).
Closed Request Count = CALCULATE([Request Count];FILTER('311_Details';'311_Details'[Nature]<>"Information"))
Where
Request Count = COUNT('311_Details'[DDS])
But when looking at open_date, we can see that there is no active relationship with the generic time dimension
But when adding a measure that enforces the use of the relationship, like:
Created Request Count = CALCULATE([Closed Request Count];USERELATIONSHIP('311_Details'[Open Date];AllDates[Date])).
We see that now looking at date or close date becomes the same thing
When adding all dates to the grid the data still looks like expected
Even when removing the close date
But I can’t figure out why the number explodes when we only put the generic time dimension on the grid
Ultimately what I need is to see for a given date
How many closed request we have (through the date/closed date relationship)
How many open request we have (through the date/open date relationship)
Basically what I’d like to see: is
Date | Created Request Count | Closed Request Count
1 Jan 2014 | 76 | 24
