Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have multiple tables I created using the Power Query tool. All tables are based on data from a "Master Dataset". I would like to be able to filter the Master Dataset using a scenario slicer and have all other tables in the workbook recalculate based on that filter.
Thanks for any help on this!
Solved! Go to Solution.
Assuming your input is in a table format
(1) Convert "Master Dataset" into a table Insert...table...
(2) Click in table then Insert...Slicer...to add slicers; don't make any selections yet
(3) Add additional formula in blank column of table :
=(AGGREGATE(3, 5, [@Sales])>0)+0
replacing "Sales" with appropriate header row from your table
(3) Create powerquery based on that table, including the extra column
(4) add PowerQuery filter that removes rows where extra column equals 1. Those are the hidden rows that you filtered out of dataset. Example:
#"Filtered Rows" = Table.SelectRows(#"Previous Step", each ([Column1] = 1))
(5) Base your output tables on the filtered input
(6) After changing any slicer values, re-caclcuate PowerQuery to reflect new data
Assuming your input is in a table format
(1) Convert "Master Dataset" into a table Insert...table...
(2) Click in table then Insert...Slicer...to add slicers; don't make any selections yet
(3) Add additional formula in blank column of table :
=(AGGREGATE(3, 5, [@Sales])>0)+0
replacing "Sales" with appropriate header row from your table
(3) Create powerquery based on that table, including the extra column
(4) add PowerQuery filter that removes rows where extra column equals 1. Those are the hidden rows that you filtered out of dataset. Example:
#"Filtered Rows" = Table.SelectRows(#"Previous Step", each ([Column1] = 1))
(5) Base your output tables on the filtered input
(6) After changing any slicer values, re-caclcuate PowerQuery to reflect new data
Thank you so much for your answer! This worked great!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |