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
Extending Phil_Seamark solution to use calculate table without any aggregation and can be used for new table
CALCULATETABLE (
'Product Data',
FILTER ( 'Product Data', 'Product Data'[ProductCategory] = "Mountain Bikes" ),
FILTER ( 'Sales Data', 'Sales Data'[Unit Price] > 1000 )
)Thanks,
Harry