One date slicer for cases assigned and cases closed averages

[jaynefish]

I currently have the below visual, but have been asked if it's possible to just have one date filter, which I believe it is from all of the reading on these forums, but I can't seem to make the DAX work. The Assigned Date and Closed Date are in the same table.

The columns displayed are as follows:

• Owner is owneridname
• Weekly Target is a calculated column based on FTE of staff
• Assigned/Closed Average is a measure - Close AVG = AVERAGEX(SUMMARIZE('All Employees & Throughput','All Employees & Throughput'[Closed Week&Year]),CALCULATE(COUNTROWS('All Employees & Throughput')))
• Close Target % is a measure - Close Target % = DIVIDE (('All Employees & Throughput'[Close AVG] ), MIN ('All Employees'[Weekly Assigned/Close upper target]),BLANK())

I have looked at multiple threads on the community and followed instructions on creating relationships with a calendar table as well as trying USERELATIONSHIP, but I'm a novice at DAX and can't get it to work. Any help would be much appreciated

[jaynefish]

Thanks Ibendlin

That's how I have the relationships set up. I'm struggling with the correct syntax for USERELATIONSHIP to make it work.

I've tried the following:

Closed cases = Calculate(COUNTA('All Employees & Throughput'[closed date]), USERELATIONSHIP('All Employees & Throughput'[closed date],'Calendar Table'[Date])).

I'm sure I'm missing a step.

[lbendlin]

Your code seems to be correct. Maybe there is something else going on. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[jaynefish]

Thanks for confirming that Ibendlin, it's working now. I double checked all my dependent measure and made adjustments where needed.

[lbendlin · Accepted solution]

That is a common pattern where your fact table is linked to the Calendar table twice, once with an active relationship on the Assigned Date and then with an inactive relationship on the Closed Date.  Then you use USERELATIONSHIP to modify the data model for your close measures.