Can I filter a Field in a slicer?

[nmss18]

Hello,
I am analzying data for a monitoring system. 2 million plus records.

In the values, I have the count of alerts
In the row labes box, I have the month
In one of the slicers I placed the Policy ID field.

the problem is, there are over 500 policy IDs in place, and I would only like to analyze those policy IDs which generated over 100K alerts.
I can filter this data when I place the 'Policy ID' field in the 'row labels' or in the 'report filter' however, but i cannot figure out how to filter this when the field is in the slicers.

Can anyone assist?
much appreciated.
Nathan

 

[MD610]

There is currently no way to directly filter the values displayed in a slicer.

The work around is to create a new calculated field in the PowerPivot table (or base data) that only displays the values you want to see and then set that to a slicer.

You could do this with a DAX equation which would be an IF statement that checks for the value of the count and returns the policy ID if over 100,000 and a blank if not. Or it could just return TRUE if over and FALSE if under then you would could filter on that before narrowing it down by ID.

You could add such a field to the base data if you have access to manipulate it.

Also, I don't know how your data is formatted but if coming up with an effective DAX equation to check the count is proving to be tough, you could create a pivot that just calculates the total count for each ID. Then copy the pivot and paste special>values into a new blank excel sheet. Link this new sheet back into PowerPivot and join it by ID to your original data. Then you could use the RELATED function to pull the counts into your original table.

Again, without knowing the structure of your base data and your comfort level with various methods its hard to know which way would be easiest. One thing I know is that slicers are easiest to work with on fields that don't have a huge number of distinct values.

Alternatively, maybe you could structure your pivot differently so that slicing on ID wouldn't be needed? What if ID's were in the row and months were across the columns? Then you could sort by the count totals.

 

[nmss18]

Worked! i created a pivot table and filtered the data to display the top generating policy ID's. I then copied it to another worksheet and uploaded it into powerpivot and just linked the 2 columns and when I placed it in the slicer it worked perfectly.
Thanks again for the idea.