Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

USERELATIONSHIP with multiple dates

I have a list of services with IDs that have an open date and a closed date on the same record line in a table. They look like so:

IDOpened DateClosed Date
110/22/201911/4/2019
211/4/201911/4/2019
311/4/201911/6/2019
411/5/201911/7/2019
511/7/201911/12/2019
611/8/201912/17/2019
711/13/2019 
811/13/2019 
911/19/20191/3/2020

 

I've created a Calendar table with an Active Relationship from the Date column to Opened Date column in my dataset, as the majority of sorting I'll do with the expanded dataset uses that column. 

If I wanted to take a look at all the services that were opened (created) in the month of November, I'd set my slicer from the Calendar table with a minimum date of 11/1/2019 and a maximum date of 11/30/2019 then create a Measure:

 

Count of Opened Cases = COUNTROWS(ID)

 

This gives me a total of 8, which is great! When I took specifically at the data to validate, I like what I see:

 

IDOpened DateClosed Date
211/4/201911/4/2019
311/4/201911/6/2019
411/5/201911/7/2019
511/7/201911/12/2019
611/8/201912/17/2019
711/13/2019 
811/13/2019 
911/19/20191/3/2020

 

Now I want to see the services that were CLOSED in the month of November. Similar counting methodology, but within a given date range with the Closed Date column. Since the Calendar table already has an active relationship with the Opened Date column, I create an inactive relationship to the Closed Date column with the Date column from the Calendar table, and then use the USERELATIONSHIP function within a CALCULATE function to do a similar count:

 

Count of Closed Cases = CALCULATE(COUNTROWS(ID), USERELATIONSHIP(Services[Closed Date],'Calendar'[Date]))

 

The expected total I should get is 5, as these should be the services that come up:

 

IDOpened DateClosed Date
110/22/201911/4/2019
211/4/201911/4/2019
311/4/201911/6/2019
411/5/201911/7/2019
511/7/201911/12/2019

 

However, when I display my count, it's 8, the same as my Count of Opened Cases measure, and the list that remains is the same one as the Opened Date cases.

 

My guess is the date slicer always defaults to the Opened Date column regardless of the measures I've made? Can anyone provide any guidance on how to move forward?

  • Hi Anonymous,

     

    you need to count the Facttable "Services" and not on a Dimensiontable "ID".

     

    You may download my PBIX file from here.
    Hope this helps.

     

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

3 Replies