Power Pivot - Creating slicer from dynamic lookup table

Hello,

I have built a dashboard based on multiple data sources and wanted to be able to to filter those data sources with one single slicer. Therefore I have created different lookup tables in the data model

Schermafbeelding 2022-09-21 115911.png

The picture below shows one of the dynamic lookup tables that is loaded into the data model. In this case there is only 1 contractnumber, but it might occur that there are several and the [nvt] will make place for the new contractnumbers based on a formula. The problem is that these 5 slicers based on the shared lookup tables from the picture keep showing items with no data. Normally, with a slicer for a single data source, you can turn this off in slicer settings. Now the slicer shows the actual contract number 2019-68377, BUT also all the [nvt] ones (nvt means N/A actually).

Is there a better way to make dynamic tables that scale with more or less contractnumbers or to hide the slicer items with no data?

Schermafbeelding 2022-09-21 120217.png


The slicer below shows as if all the [nvt] items have actual data, but they do not. Slicer settings --> hide items with no data does not seem to work for data model slicers..?
Schermafbeelding 2022-09-21 120632.png

Thank you for your reply.






Let me further explain the way I was planning to build the dashboard in sequential steps:

1. Data source with with 10k rows (might contain more than 1 unique contract number)
2. PivotTable: shows list of unique contractnumbers based on data source from step 1
3. Because a pivot table cannot be added to the data model in Power Pivot, I made a 'normal' table that references the pivot table with formula =IF(cell in pivot table <>""; cell in pivot table; [nvt]. I then added this table to the data model

When I would delete P3:P14 and only leave 2019-68377 in P2, the table would then not automatically expand when more contracts are loaded in through the pivot table, right? I have tried everything but the table reading directly from the data source or from the pivot table does not seem to get longer whenever I refresh the data.