Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
gtamir1
Helper I
Helper I

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?

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @gtamir1 ,

 

I don't have access to the link you shared, so I created the following sample data.

 

vkkfmsft_0-1644975259788.png

vkkfmsft_1-1644975297739.png

 

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.

vkkfmsft_7-1644976836662.png                    vkkfmsft_8-1644976861721.png

 

2. Create a parameter and references query Year_Month.

 

vkkfmsft_9-1644976904994.png

 

3. Filter the rows in column [Year_Month] equal to the parameter.

 

vkkfmsft_3-1644975373023.png      vkkfmsft_4-1644975413776.png

 

4. Then you can modify the parameter to change the data loaded into the model.

 

vkkfmsft_5-1644975522636.png            vkkfmsft_10-1644976970819.png

 

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.

View solution in original post

13 REPLIES 13
v-kkf-msft
Community Support
Community Support

Hi @gtamir1 ,

 

I don't have access to the link you shared, so I created the following sample data.

 

vkkfmsft_0-1644975259788.png

vkkfmsft_1-1644975297739.png

 

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.

vkkfmsft_7-1644976836662.png                    vkkfmsft_8-1644976861721.png

 

2. Create a parameter and references query Year_Month.

 

vkkfmsft_9-1644976904994.png

 

3. Filter the rows in column [Year_Month] equal to the parameter.

 

vkkfmsft_3-1644975373023.png      vkkfmsft_4-1644975413776.png

 

4. Then you can modify the parameter to change the data loaded into the model.

 

vkkfmsft_5-1644975522636.png            vkkfmsft_10-1644976970819.png

 

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.

@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.

@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

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. 

Kumail
Post Prodigy
Post Prodigy

Hello @gtamir1 

 

The options given by @bcdobbs almost covers all the scenarios.

 

If you could send sample .pbix file, this would enable to better understand your requirements and giving a working solution.

 

Regards

Kumail Raza

Thanks @Kumail I granted your permition request for google drive.

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

Hello @gtamir1 

 

The image is attached for your reference.

Kumail_1-1645287855512.png

https://drive.google.com/file/d/1JBcpiPsdnW0kpfupiZktCPi2fNLNicTK/view?usp=sharing

 

Regards

Kumail Raza

Did this help? Kudos are appreciated

Consider Accept it as the solution to help the other members find it more quickly

 

 

Hi @Kumail 
Thank you for your effort.

I don't know what you did but the numbers are incorrect.
If I filter the creditcard file in power querry for one year, this is what @v-kkf-msft does too with a parameter, I then count cards in "C" in the summarized table and get different numbers then yours.
You can export it to excel and play with it.

Hello @gtamir1 

 

Count of Type A comes from another DAX table Running total by credit card which doesn't have dates. Therefore, in order to get the dates working, it was linked to the original credit card table with bi filtering in the model. When you filter the records by dates, the credit card table filters the DAX table "Running total by credit card" and counts the records as per your defined logic. 

 

Data in Power Query Editor is not available since it requires credentials to access.

Kumail_0-1645369324540.png

 

I hope this helps. 

 

Regards

Kumail Raza

Did this help? Kudos are appreciated

Consider Accept it as the solution to help the other members find it more quickly.

Hello @gtamir1 

 

The table referred to in the file seems missing. 

Kumail_0-1645286521214.png

 

 

Yes I just sent the importent tables. "creditcard" is the sales, and 

"RunningTotalByCreditCard".
I want to filter 
RunningTotalByCreditCard by month(s).
@v-kkf-msft gave me a solution but I would like to multiple filter, and to leave on tab unfiltered.

 

bcdobbs
Super User
Super User

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.

 

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.