Use slicer to exclude and show rows excluded

[Anonymous]

I want the slicer to display all the customers that have not been visited between a certain period- 

I have a calculated column 

LastVisitDate = MAXX(FILTER('All calls', 'All calls'[customer ID]= EARLIER('All calls'[customer ID])), ''All calls'[Date])

I tried calculating a column but the result is always "No" . I have added a "between" slicer too

Not Visited = IF ( ISBLANK ( CALCULATE ( MAX ( 'All calls'[Date]) ), FILTER (  'All calls', YEAR ( 'All calls'[Date] ) = YEAR ( 'All calls'[LastVisitDate] ) &&  MONTH ( 'All calls'[Date] ) = MONTH ('All calls'[LastVisitDate] ) &&  'All calls'[customer ID] = EARLIER('All calls'[customer ID]) ) ) ), "Yes",  "No" )

I tried finding the solution but couldn't figure out how to apply them to my own case- I would appreciate your help!

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

You can create measures to achieve your desired results.

Here are the steps you can follow：

1. You need to create a table of unrelated dates to act as a slicer.

Date=
CALENDAR(
    DATE(
        2021,1,1),
    DATE(
        2021,4,1))

2. Create measure.

Flag =
var _mindate=MINX(ALLSELECTED('Date'),'Date'[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),'Date'[Date])
return
IF(
    MAX('All calls'[customer ID]) in SELECTCOLUMNS(FILTER(ALL('All calls'),'All calls'[Date]>=_mindate&&'All calls'[Date]<=_maxdate),"test",'All calls'[customer ID])
,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

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