Need help with Slicer

[ningles]

Hello,

I have a dataset with summarized call data by Date, Agent, and FCR Type.

The FCR Types are as follows:
FCR2 = Call that did not lead to another call within 2 days.
FCR10 = Call that did not lead to another call within 10 days.
FCR30 = Call that did not lead to another call within 30 days.

In my report, I've set up slicers for the Date and FCR Types above, which work fine. I am able to show the three FCR Types by using a single select slicer, however, the requirement is to only show data for the different FCR Types based on a dynamic date range for each type.

For example, when FCR2 is selected in the FCR Type slicer, I only want the data in the report, as well as in the date slicer to show all dates for FCR2 up to 2 days before the MAX(Date) in the dataset.
Since the Max(Date) is 10/24, the expected result when selecting FCR2 in the slicer is to show data on or before 10/22:

When FCR10 is selected I’d like data up to 10 days before the MAX(date):

...and when selecting FCR30 I’d like it to show data up to 30 days before the Max(Date):

Is there a measure that would be able to accomplish this?

[v-tangjie-msft · Accepted solution]

Hi @ningles ,

According to your description, here are my steps you can follow as a solution.

(1)My test data is the same as yours.

(2) We can create a table.

Slicer = VALUES('Table'[FCRType]) 

(3) We can create a measure. 

Flag = 
var _a=CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),'Table'[FCRType]="FCR2"))-2
var _b=CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),'Table'[FCRType]="FCR10"))-10
var _c=CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),'Table'[FCRType]="FCR30"))-30
return SWITCH(TRUE(),
ISFILTERED(Slicer[FCRType])=FALSE(),1,
SELECTEDVALUE('Slicer'[FCRType])="FCR2" && MAX('Table'[FCRType])="FCR2" && MAX('Table'[Date])<=_a,1,
SELECTEDVALUE('Slicer'[FCRType])="FCR10" && MAX('Table'[FCRType])="FCR10" && MAX('Table'[Date])<=_b,1,
SELECTEDVALUE('Slicer'[FCRType])="FCR30" && MAX('Table'[FCRType])="FCR30" && MAX('Table'[Date])<=_c,1,0)

(4) Place the fields of the new table [Slicer] on the slicer and [Flag=1] on the table visual object.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...