PowerPivot Table - How To Stop Refresh From Resetting Filters

[Anonymous]

The following function for a calculated column, outputs time intervals based on time difference between two dates

=IF(WEEKDAY(FF_August_Dataset[JOB_BOOKING_DATETIME],2)=6 || WEEKDAY(FF_August_Dataset[JOB_BOOKING_DATETIME],2)=7, "",IF(
NOT ISBLANK( FF_August_Dataset[ACCEPTANCE_DATETIME] ),
VAR _hrs = HOUR( FF_August_Dataset[ACCEPTANCE_DATETIME] - FF_August_Dataset[JOB_BOOKING_DATETIME] )
RETURN
SWITCH(
TRUE(),
_hrs >= 0 && _hrs <= 2, "0-2hrs",
_hrs >= 3 && _hrs <= 4, "3-4hrs",
_hrs >= 5 && _hrs <= 12, "5-12hrs",
_hrs >= 13 && _hrs <= 23, "13-23hrs",
_hrs>=24 && _hrs<=48, "24-48hrs",
">48hrs"
)
))

As per screenshots below of pivot table below, the output values include "(blank)" and blank with no value. Because of this, I believe it is messing up my slicers i.e. even with "Hide items with no data" ticked, it is still showing in slicer. How do I solve this issue?

 

[Nathaniel_C]

Hi @Anonymous ,

Have you tried filtering the slicer? I believe what you are doing is filtering the table. 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

@Nathaniel_C  I did filter the pivot table to NOT show "(blank)" and the other blank no value hoping it would solve issue but it didn't as it would reset when slicer values were changed.

And what do you mean by filtering the slicer as I couldn't find any option to do so as per screenshot below unless I'm missing something?