USERELATIONSHIP with Date slicer

Anonymous — Wed, 18 May 2022 14:21:46 GMT

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!

Re: USERELATIONSHIP with Date slicer

amitchandak — Wed, 18 May 2022 15:26:33 GMT

Anonymous , Refer my blog on the same topic

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

Attached file after signature if needed

Re: USERELATIONSHIP with Date slicer

Anonymous — Sun, 22 May 2022 14:54:09 GMT

thanks amitchandak

I don't think I wrote the DAX in the measures to be optimal, but your article got me to a working solution 👍

topic Re: USERELATIONSHIP with Date slicer in DAX Commands and Tips

USERELATIONSHIP with Date slicer

Re: USERELATIONSHIP with Date slicer

Re: USERELATIONSHIP with Date slicer