How to clear old Pivot table items in filter lists (powerpivot pivot table) ?

[Lolo]

Hello,

I'm facing a problem with a pivot table that is linked to a power pivot model.
Twhen I try to filter a pivot item, Isee a lot of old unused items.

While on a standard pivot table, I know how to remove them, a setting in the properties of the pivot table, this setting is disabled in this case, and I don't know how I could remove old pivot items in my filter lists

Is someone already experienced that ?
Thank you

 

[GraH - Guido]

Hi Lolo, I never noticed that. To my understanding, if the field items are not present in the data model, they do not show up in the filters. At least that is what I expect.
When you say unused... From which table in your model are the items populated? Your transaction or lookup table? Are you absolutely sure they are not present in the data model?
Otherwise I really do not have a clue how to solve this. I just expect the issue not to occur when working with power pivot. Puzzling...

 

[Lolo]

Actually You are right,
I use a field of a dimension table where my value is present.

BUT, there is NO data (from my fact table) related to this item !
If I add a slicer for this field, I can only see the items with related data, while it is not possible in the filter. (see picture attached, where we can see that 'APL USE-FELIXSTOWE -CFXCOS' is selectable in the pivot filter(while I don't want to see it), while in the slicer it is greyed out, and put at the end of the slicer, so I cannot pick it in the "valid"' selectable values.)

Is there any option to "hide" unused values into the standard pivot item filter list ?

 

[GraH - Guido]

But it means it is in your data model. That is why it is in the filters and even on the slicers. But slicers have extra options which standard filters have not.


Note that the first one is "dangerous" to use in a power pivot model. It actually has a negative effect on the performance, as Rob Collie explains in one of his books. Each slicer coordinates with the other slicers and VertiPaq (the DAX engine) needs to calculate for each of the "items" the ones "with no data" for each slicers and for each row/column coordinate of the pivot. On smaller data sets, VertiPaq is big enough to handle it. On larger data sets however, even VertiPaq has difficulties to manage that swiftly.

In your case, ... what if, you put the field from your fact table in the filter. Now generally speaking this is ill advice, since VertiPaq now has to calculate the distinct list. Or use a calc column in your dim table that verifies if a value is used in the fact table. Again not the best of things.

 

[GraH - Guido]

EDIT/correction of previous: VertiPaq does calculate the unique values during data load, so it might be not so bad from performance point of view. However it is not the best practice. It goes against the concept of relationships travelling down filter context. And.. you do not notice dimensions without factual data...

 

[Lolo]

Thank you for your answer.

For me, it is a basic thing to filter facts data by a dimension table. I do the model for that I would say...

Filter on the fact field is not really user friendly, the vessel_Id field is a code, so not really possible to filter on it.

If the slicer had (like in qlikview) a text search ability, I would use the slicer and not the pivot table filter field...
I have not this kind of problem in Qlikview.

Seems we cannot have the best of the 2 objects (slicer and ability to do a text filter in it), and this is annoying from a user point of view
Instead of that we are exposed to the limits on the 2 objects :
* Slicer : ability to hide values (no perf issue in my case) but no text filter : Problem in case of "big list"
* Pivot item filter : Text filter, but no option to remove/hide unused items : no clean filter data

Because of that we are discussing on workarounds, but I'm surprised that there is not an easy way to find a solution for such a basic request

 

[Chihiro]

Can you upload sample?

There's few ways to do it.
1. Dynamic table created using M to base your slicer on (using condition to calculate what values should be shown).
2. Code to push data to data model and update slicer.
etc.

 

[GraH - Guido]

Chihiro,given Lolo is not using Excel 2010. Otherwise getting from PQ to PP is rather difficult (long procedure,that is not even supported by Microsoft).
But indeed, why didn't I suggest PQ before?

Lolo, nice to see you are applying and aware of data model best practices. Good job.
I was actually thinking of a calculated column where you use related and it would populate in the fact table the labels you need from the dim table. The same what Chihiro proposes via PQ. This is a better option because you "correct" your dim table that way. And you do not need an extra "lookup" column in the data model fact table, which is by definition redundant.

 

[Lolo]

Well noted,

However not sure to understand your proposal.
Currently I use PQ to load data (fact and dimension) from my DB. So do you mean to :
1. add in PQ a calculated field (a flag column) in my dimension table to filter only the one that have data ?
2. (in PQ) Filter and removed unused data from the dimension table ?

Could be an option indeed. But this will add extra loading time however, because my fact table is big, with already a lot of PQ statements, and sometimes PQ statements could be looong to execute in Excel. To be checked.

Do you need I send a test file ?

NB: I'm using Excel 2016

 

[GraH - Guido]

XL 2016, then go with the PQ solution to "clean-up" your dim table so it only contains those filter records that are actually in your fact table.
Indeed option 2. The calculated column I refer to was the option in the PP data model. In the fact table:=RELATED(DimTable, DimTable[LookupColumn]). Or in the dim table use RELATEDTABLE(FactTable, FactTable[LookupColumn]) and use this field the filter.

PQ steps add to loading time? Yes probably. And the calculated column consumes memory to store the data in PP.
So it kind of depends on what is possible and helping you the most.
Or Chihiro (or someone else) has other ideas?

 

[Chihiro]

It really depends. On model and what optimization steps are taken.

Typically speaking, I usually don't want full detail of fact table and aggregate it even before PQ stage (in SQL usually).

When that isn't feasible, I set it so that refresh of data only occurs once at workbook open, and data remains static after initial load for analysis. Avoid Calculated column in DAX at all cost, as that typically has pretty significant impact on model performance.

If really necessary, I use VBA to push data to model and create relationship. Though not recommended if you are not experienced in it.

 

[GraH - Guido]

Wise man! Experience talking...