Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ningles
Regular Visitor

Need help with Slicer

Hello,

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

ningles_0-1698356727883.png


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:

ningles_6-1698358019708.png

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

 

ningles_5-1698357929116.png

 


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

ningles_4-1698357664846.png

 


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

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1698649255417.png

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. 

View solution in original post

2 REPLIES 2
v-tangjie-msft
Community Support
Community Support

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.

vtangjiemsft_0-1698649255417.png

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
Super User
Super User

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...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.