How to filter paginated report on distinctcounts from a powerbi dataset

[HannesB]

Hi,

I have a paginated report that retrieves data from a Power BI dataset. I want to apply a filter to the data in the paginated report based on the "Count" column from my Power BI dataset. To achieve this, I have created a calculated table using the following DAX expression and have included a screenshot of the data:

CountTable = FILTER(SUMMARIZE('Fact', 'Fact'[ArticleID], "Count", DISTINCTCOUNT('Fact'[ItemID])), NOT(ISBLANK('Fact'[ArticleID])))

The purpose of the CountTable is to count the number of products associated with each article.

When I use the count column as a filter in Power BI, I only see one unique value per count. For example, in the table, the number 2 will only appear once like this.

In the paginated report, I retrieve the data using the following query within a dataset:

(It is here I think I need to change someting If not in the Powerbi Dataset. For SQL I would assume that it works with SELECT DISTINCT Count from Table.)

EVALUATE
SUMMARIZECOLUMNS (
'CountTable'[ArticleID],
'CountTable'[Count]
)
ORDER BY
"Count"

The filter in the paginated will then have a count for each article and not just one distinct 2 and one distinct 3 etc.

Does anyone ave any suggestions on how I could handle this filter in the paginated report?

Thank's in advance.