Forum Discussion
filtering summarized table
Hi, I have a summarized table based on Sales table. The summarized table does not have dates.
I wand to show graphes from the summarized table, filter by months.
If I use a slicer with months for the sales table, but the summarized table does not change acording to the slicer (does not re-generated).
The only way I can do this is to filter the sales table in Query Editor.
Any better idea?
Hi gtamir1 ,
I don't have access to the link you shared, so I created the following sample data.
You can try using the query parameters to filter the months.
1. First add column [Year_Month] as a new query, then remove the duplicate values.2. Create a parameter and references query Year_Month.
3. Filter the rows in column [Year_Month] equal to the parameter.
4. Then you can modify the parameter to change the data loaded into the model.
Alternatively, if you are using the DirectQuery mode and the data source meets the following restrictions, then you can try using the Dynamic M query parameters.
- The feature is only supported for M based data sources. The following DirectQuery sources are not supported:
- T-SQL based data sources: SQL Server, Azure SQL Database, Synapse SQL pools (such as Azure Synapse Analytics (formerly SQL Data Warehouse)), and Synapse SQL OnDemand pools
- Live connect data sources: Azure Analysis Services, SQL Server Analysis Services, Power BI Datasets
- Other unsupported data sources: Oracle, Teradata, and Relational SAP Hana, PostgreSQL
- Partially supported through XMLA / TOM endpoint programmability: SAP BW and SAP Hana
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
13 Replies
Hi gtamir1 ,
I don't have access to the link you shared, so I created the following sample data.
You can try using the query parameters to filter the months.
1. First add column [Year_Month] as a new query, then remove the duplicate values.2. Create a parameter and references query Year_Month.
3. Filter the rows in column [Year_Month] equal to the parameter.
4. Then you can modify the parameter to change the data loaded into the model.
Alternatively, if you are using the DirectQuery mode and the data source meets the following restrictions, then you can try using the Dynamic M query parameters.
- The feature is only supported for M based data sources. The following DirectQuery sources are not supported:
- T-SQL based data sources: SQL Server, Azure SQL Database, Synapse SQL pools (such as Azure Synapse Analytics (formerly SQL Data Warehouse)), and Synapse SQL OnDemand pools
- Live connect data sources: Azure Analysis Services, SQL Server Analysis Services, Power BI Datasets
- Other unsupported data sources: Oracle, Teradata, and Relational SAP Hana, PostgreSQL
- Partially supported through XMLA / TOM endpoint programmability: SAP BW and SAP Hana
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thnks a lot. I'll write you on sunday because I am not with my laptop.
What is the problem with the link, maybe you should ask for ppermition.
v-kkf-msft Interesting solution.
What to do for multiple selection?
Also I'd like to see all period in one tab and filtered period on other tabs.
Thanks
v-kkf-msft I tested it, it werks.
Now I have to find the way to "no parameter" and away to choose multiple values.Thank you.
Can you tell us a bit about your architecture?
Do you have a full sales table in a source system and you're only importing a summarised version?
Tables don't recalculate based on slicers. They're loaded at processing time.
You have some options:
1) Import the full sales table, if load time is an issue look at incremental load.
2) Import a table that is still summarised but only down to the grain you need. (Eg group by month/year in your case.
3 Explore direct query options. Leaving data in the source system.
Thanks Kumail . Here is a link to the file. The slicer will not filter the 3 top numbers because they come from Running total file. How can I cause the running Total file to recalculate upon filter change?
https://drive.google.com/file/d/1hVwYy8IaeOeLcSagpFtkNRlxkaLZVLj7/view?usp=sharing
