Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

USERELATIONSHIP with Date slicer

Hi,

 

I'm having trouble with my date slicer to coincide with the measures I have in a clustered column chart.

 

Goal - I want to see on the clustered column chart, 2 measures: people who start, 'Starters', and people who left the company, 'Leavers'. These are on the Y axis. The area of the company they left is on the X axis.

 

Measures being used:

 

Starters =
var startermeasure = calculate(DISTINCTCOUNT('All Postholding w/ Projected End Date History'[Employee Number]), 'All Postholding w/ Projected End Date History'[starter] = 1)
var starterempstartdate = CALCULATE(startermeasure, USERELATIONSHIP('Date'[Date],'All Postholding w/ Projected End Date History'[Emp Start Date]))

return starterempstartdate
 
Leavers =
var leavermeasure = calculate(DISTINCTCOUNT('All Postholding w/ Projected End Date History'[Employee Number]), 'All Postholding w/ Projected End Date History'[leaver] = 1)
var leaverempenddate = CALCULATE(leavermeasure, USERELATIONSHIP('Date'[Date],'All Postholding w/ Projected End Date History'[ph end Date]))

return leaverempenddate
 
Here is the data model, 'Date'[Date] column with active relationship to 'All Postholding w/ Projected End Date History'[Original Start Date] and inactive relationships for measures above to work:

 

 

The measures seem to work, but the issue is when I put a date slicer on the page; as it's still using whichever relationship is active, it's filtering the visuals to the incorrect numbers; e.g: if the 'Dates'[Date] to 'All Postholding w/ Projected End Date History'[ph end Date] is active, the leavers number is correct but the starters is wrong and I get the following:

 

S= Starters, L = Leavers

Drill-through to a 'Leavers - Data' page where the 'Leavers' measure goes to and has table above

 

 

But then if Date'[Date] to 'All Postholding w/ Projected End Date History'[Emp Start Date] relationship is active I get the following:

 

 

Drill-through to a 'Starters - Data' page where the 'Starters' measure goes to and has table above.

 

 

How do I get the date slicer to just let me pick between two dates, and I get the correct number of starters or leavers on the column chart, and when I right-click drill-through, the relevant measure I'm drilling through with takes me to the appropriate page with the right data?

 

Thanks in advance, I hope all the information is clear enough!

 

 

 

 

 

2 Replies