Forum Discussion
FILTER on multiple tables
Hi andrewsk
As a NEW TABLE, try this
New Table= FILTER ( 'Product Data', 'Product Data'[ProductCategory] = "Mountain Bikes" && CALCULATE ( AVERAGE ( 'Sales Data'[Unit Price] ) > 1000 ) )
FILTER function is an interator. All ITERATORs (like FILTER,SUMX,MAXX etc) work in folllowing fashion.
1) They create a ROW context on the TABLE received as first argument2) They evaluate the second expression for each ROW of that TABLE
However FILTER only provides ROW context and not the FILTER context.
Calculate transforms that row context into filter context allowing you to work with relationships
Hi andrewsk
As a NEW TABLE, try this
New Table=
FILTER (
'Product Data',
'Product Data'[ProductCategory] = "Mountain Bikes"
&& CALCULATE ( AVERAGE ( 'Sales Data'[Unit Price] ) > 1000 )
)
FILTER function is an interator. All ITERATORs (like FILTER,SUMX,MAXX etc) work in folllowing fashion.
1) They create a ROW context on the TABLE received as first argument
2) They evaluate the second expression for each ROW of that TABLE
However FILTER only provides ROW context and not the FILTER context.
Calculate transforms that row context into filter context allowing you to work with relationships
Thank you for your reply. I have a final question if you don't mind.
So in your example:
New Table=
FILTER (
'Product Data',
'Product Data'[ProductCategory] = "Mountain Bikes"
&& CALCULATE ( AVERAGE ( 'Sales Data'[Unit Price] ) > 1000 )
)
When you are using AVERAGE, is it just to trick the code into using the FILTER context?
The actual data returned from this would be AVERAGED for each row, so it would not change. (IE the Average of a Unit Price of $1500.00 on a single row is still $1500.00. ) :smileyhappy:
Hi andrewsk
I believe this is how it works.
Assuming there is a one to many relationship between Product and Salesdata
1) The argument "CALCULATE ( AVERAGE ( 'Sales Data'[Unit Price] ) > 1000" is evaluated for each ROW of the PRODUCT Table.
2) Since we use CALCULATE INSIDE a ROW CONTEXT, it transforms that ROW context into a FILTER CONTEXT.
Meaning that... for each product it goes to SALESDATA table, fetches the ROWS that are related to that product and takes an average of those products
3) It that average is >1000, the Row of the Product Table is retained
else it is filtered out