Perform Group By Aggregation After User-Input Filter from Dashboard

[brspencer90]

Hi all - 

Looking to see if it is at all possible to perform a "Dynamic" Group By Aggregation AFTER filtering the data based on criteria set by the user from PowerBI.

Essentially :

• Use filter in PowerBI dashboard
• Filters Query #1 (Q1) in "Data" tab on left hand side
• PowerQuery performs Group By / Aggfunc on that data (Q1) => Q2
• Use Q2 data in PowerBI Dashboard

I understand this would increase the computing strain on PowerBI so was thinking there might be an option to "compute" after selecting filters.

I can understand how to make a Measure to check if the filter affects a row (TRUE / FALSE), but that doesn't change the fact that I need the group by / aggregate to work after the filter is applied.

This is a bit of a long shot - I have a feeling there's not an option for this. I have been using more coded options to pull this aggregate data from a database, however, I can't do that based on user input from the dashboard.

[brspencer90]

Thanks Tom for your reply. I am currently using DirectQuery so this is an option. 

I've thought about creating a new DAX table and seeing if that works as a simpler option - however that might mean more code to my 60 value columns :-).

[TomMartens]

Hey @brspencer90 ,

be aware that MSFT SQL Server currently does not support "Dynamic parameters".

Also, DAX tables will not be "re-calculated" on user interaction.

Good Luck,

Tom

[brspencer90]

Tom - 

Have done some schmelting (actually came upon one of your old posts that helped me devise something that works - but also doesn't). 

I was able to use a MEASURE to do basic averages on data tables that ALSO use the slicers on the dashboard.

Measures : 

ID = DISTINCT(SELECTCOLUMNS(df_query,"ID",[ID]))

Value = AVERAGEX(df_query,df_query[Value])

ValueToRank = AVERAGEX(df_query_other,df_query_other[Value])

df_query_other and df_query are not related. df_query_other is filtered by a slicer equivalent to ID = 380

I created a visualisation table which does EXACTLY what I want to do when I plot the Measure Value and the ID column.

I want to then filter the df_query data and have a dynamic min and max on the KPI Gauge Viz. Unfortunately when I add the Value to the Gauge, it doesn't allow me to summarize (min / max) in the Visualisations Field Tab. 

I now know I can't do that with a measure, so I need to figure out a few things : 

• Can I replicate this dynamic visualisation table (interacts with filter) using DAX - not to store but to pull data from to create additional?
• How do I get a min / max value from a aggregate measure based on an index?
• Is it feasible for me to do this for 60 features (that's a me question not a you question)?

I feel like I'm so close, but yet so far away. If only I could get the raw data from the Viz table I'd be set!

[TomMartens]

Hey @brspencer90 ,

unfortunately, there is a simple answer to your question - this is not possible.

This is because of the layered components Power Query queries -> Data model (tables, relationships, calculated columns) -> Measures <-> User interaction (slice & dice) con be considered a one-way path, just measures will be "re-calculated" if the filter context changes (e.g. by changing the slicer selection).

Filtering is different than re-calculating, for this reason both "activities" must not be confused. 

Depending on your data source you can use Dynamic Power Query parameters, here slicer selections will be "injected" into your queries if you are using the DirectQuery connection mode: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#:~:text=Dy...

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom

Depending on your data source