topic Re: Using GROUPBY with dynamic filter context in DAX Commands and Tips

Using GROUPBY with dynamic filter context

Anonymous — Tue, 01 Oct 2019 13:37:48 GMT

Hi all,

I'm struggling with the GROUPBY() function. I would like to group my data by ITEM and doing an average on the volume. The table is filtered by a slicer on the minimum date. It seems like the GROUPBY function is not considering the filter context as the average volume is the same with or without the date slicer.

Table:

ITEM | DATE | VOLUME

A; 01/01/2013; 50

A; 05/01/2013; 60

A; 05/01/2015; 70

B; 01/01/2013; 50

B; 06/02/2013; 50

B; 05/03/2015; 40

Here is the code I'm using.

=GROUPBY(
Table;
Table[ITEM];
"Average volume by item";
AVERAGEX(
CURRENTGROUP();
Table[Volume]
)
)

I would like the GROUPBY() to group the table already filtered with the date slicer, for example Date > 01/01/2017.

I should end up with this:

ITEM | AVG VOLUME

A; 70

B; 40

It's not the case and it seems like GROUPBY is ignoring the filter context.

What would be the right way to do it?

Thank you very much.

Re: Using GROUPBY with dynamic filter context

AlB — Tue, 01 Oct 2019 15:43:36 GMT

Hi Anonymous

You seem to be creating a calculated table. That is static and will not be affected by filter context at all. Try creating a table visual instead, with the appropriate measure

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Re: Using GROUPBY with dynamic filter context

Anonymous — Tue, 01 Oct 2019 16:23:04 GMT

Hi AIB, thanks for you help.

Is their a function I could use in this measure instead of GROUPBY to average on the same "ITEM"?

Re: Using GROUPBY with dynamic filter context

AlB — Tue, 01 Oct 2019 17:44:19 GMT

Anonymous

How about you just

1. Place Table[ITEM] in the rows of a matrix visual

2. Place this simple measure in the visual

Measure = 
AVERAGE( Table[Volume] )

3. Use a slicer for the date

Wouldn't that yield what you're looking far or am I completely misunderstanding?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Re: Using GROUPBY with dynamic filter context

Anonymous — Tue, 01 Oct 2019 19:45:01 GMT

Ok I get what you mean now but I need a table as an output. I'm doing further calculations based on this groupby table.

Re: Using GROUPBY with dynamic filter context

AlB — Tue, 01 Oct 2019 19:49:24 GMT

You can create a table within a measure that will be affected by filter context

Measure =

Var AuxTable_ = //Your code from earlier (GROUPBY)

RETURN

// The rest of the code using that table here

Re: Using GROUPBY with dynamic filter context

Anonymous — Fri, 11 Oct 2019 09:59:46 GMT

Hello AlB ,

Thanks for your reply. I'm trying to give it a try to your solution and I think that it could do the job but I'm struggling with the syntax.

How do you recall à created column in the GROUPBY function stocked in the variable?

VAR
Running_date= MAX(Calendar[date])
VAR
Filtered_table_to_date= FILTER(Table1; Table1[date]<=Running_date)
VAR
Group_Max=
GROUPBY(
Filtered_table_to_date;
Table1[Item];
"Max volume"; MAXX(CURRENTGROUP(); Table1[Volume]
RETURN

SUMX(Group_Max; Group_Max[Max volume])

How it should be written in the SUMX function?

Rgds,

Re: Using GROUPBY with dynamic filter context

AlB — Fri, 11 Oct 2019 16:54:00 GMT

Hi Anonymous

In this case, you have to use the column name only, without the table name:

SUMX( Group_Max; [Max volume] )

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers

Re: Using GROUPBY with dynamic filter context

Anonymous — Sat, 12 Oct 2019 08:19:39 GMT

Thank you so much, works perfectly!