cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
osinquinvdm Regular Visitor
Regular Visitor

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.

 

 2017-03-17 10_05_47-.png

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
MalS Member
Member

Re: Working with 2 time dimensions

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/ 

View solution in original post

7 REPLIES 7
MalS Member
Member

Re: Working with 2 time dimensions

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/ 

View solution in original post

Sean Super Contributor
Super Contributor

Re: Working with 2 time dimensions

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/

osinquinvdm Regular Visitor
Regular Visitor

Re: Working with 2 time dimensions

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

osinquinvdm Regular Visitor
Regular Visitor

Re: Working with 2 time dimensions

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

2017-03-21 16_47_00-311-dashboard-test - Power BI Desktop.png

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])

2017-03-21 16_46_11-311-dashboard-test - Power BI Desktop.png

 

But when looking at open_date, we can see that there is no active relationship with the generic time dimension

 

2017-03-21 16_51_20-311-dashboard-test - Power BI Desktop.png

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

2017-03-21 16_53_08-311-dashboard-test - Power BI Desktop.png

When adding all dates to the grid the data still looks like expected

 

2017-03-21 16_59_22-311-dashboard-test - Power BI Desktop.png

 

Even when removing the close date

2017-03-21 17_02_42-311-dashboard-test - Power BI Desktop.png

But I can’t figure out why the number explodes when we only put the generic time dimension on the grid

 

2017-03-21 17_04_02-311-dashboard-test - Power BI Desktop.png

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

 

MalS Member
Member

Re: Working with 2 time dimensions

Those measures look pretty good to me. (I assume that '311_details'[DDS] is just the request ID field?)

 

I would carefully check the relationships that you have set up. If possible, while troubleshooting, remove/deactivate all relationships except the active relationship between Date and Close Date, and the inactive one between Date and Open Date.

osinquinvdm Regular Visitor
Regular Visitor

Re: Working with 2 time dimensions

I looked back at that page today and I just see perferct numbers

2017-03-22 09_52_57-311-dashboard-test - Power BI Desktop.png

I swear I did not even do a refresh or reload or whatever of that nature (at least that I am aware of).

I'm actually glad I have a screenshot of the weird Created request count to prove I'm not crazy and that I did get those numbers yesterday.

I'm just glad it works today and will put that one on  IT witchcraft.

Thanks @MalS for the follow-up anyway

 

osinquinvdm Regular Visitor
Regular Visitor

Re: Working with 2 time dimensions

It is happening again !

I have been banging my head on the wall all day!

 

When only the Created request is present the date get associated with the creation date

 

2017-03-23 16_32_19-311-dashboard-cumul test - Power BI Desktop.png

But as soon I put on the grid a measure, which calculation goes through the close date, then the Created request measure "forgets" it is supposed to go through the creation date and instead sees the date as the close date, getting the data for all the creation dates associated with the current close date, thus bumping up the numbers significantly.

2017-03-23 16_32_27-311-dashboard-cumul test - Power BI Desktop.png

This is driving me nuts. Especially considering that it miraculously worked at some point

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)