Filter aggregated table with direct query datasource

[PowerToBe]

Hi, 

I have a report built on a direct query data source.

This report contains a table with products and a measure, let's say sales.

This table can become very long with hundreds of products.

How can I let the end user filter this table so that all products which have sum of sales < 100 will not be shown. It is important that this needs to be a filter on the report level, so it can be toggled on/off. Ideally the user would also be able to change 100 to whichever number he/she wants by way of a parameter, but that is of 2nd importance.

It is important to notice that the filter should be on the sum of sales, not on the single sales value of every underlying row in the sales table for that product.

Best regards! Carlos

[ToddChitt]

Ah.OK. You will probably need to change the Filter Direction of the relationship to "Both". This will allo wthe FACT table (Sales) to 'filter' the DIMENSION table (Products). Then create a Parameter (not a Power Query parameter) as available on the Modeling tab. You will need to use the SELECTEDVALUE DAX function to get the value selected by the user. Work that into you filter on Sales

[PowerToBe]

I'm sorry, I think I didn't describe the datasource correctly. My database is a SQL Server database. On top of that I have a Direct Query Power BI data model that just consists of the tables of the star schemas and the joins between the tables. And this data model is the source (Power BI dataset) of my reports. Does that change the answer to my question?

[ToddChitt]

If you are doing Direct Query, you should probably write your own SQL statement that does the GROUP BY and a HAVING clause.

Question: Do you need the details of all sales, or just the summary? If you need details, consider wrapping it all in a stored procedure that takes as its input the 'less than threshold' value. (Hint: look at WINDOWING function in T-SQL as they will allow you to get an aggregate of a group for every detail record.)

Use the Advanced Options when setting up your data source. Put in:

EXEC dbo.MyProcedure 100

Then inspect the Power Query code it generated. 

Add a Power Query parameter.

Hack the dataset to substitute the literal "100" with the Power Query parameter.