Forum Discussion
Need help with Slicer
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?
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.
2 Replies
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-Forum/ba-p/963216
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/1447523Hi 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.
