cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
qsmith83 Member
Member

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?

 

JobToAccep Pivot Filter.PNGJobToAccep Pivot Slicers NO FILTER.PNG 

JobToAccep Pivot Table.PNG

2 REPLIES 2
Super User IV
Super User IV

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

Hi @qsmith83 ,

 

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

 

showing blank.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




qsmith83 Member
Member

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

@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?

 

slicer settings.PNGslicer settings2.PNG

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors