topic PowerPivot Table - How To Stop Refresh From Resetting Filters in DAX Commands and Tips

PowerPivot Table - How To Stop Refresh From Resetting Filters

Anonymous — Wed, 25 Sep 2019 02:25:06 GMT

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?

Re: PowerPivot Table - How To Stop Refresh From Resetting Filters

Nathaniel_C — Wed, 25 Sep 2019 03:04:49 GMT

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

Re: PowerPivot Table - How To Stop Refresh From Resetting Filters

Anonymous — Wed, 25 Sep 2019 09:08:09 GMT

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?