Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

PowerPivot Table - How To Stop Refresh From Resetting Filters

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?

 

 

2 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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?