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
You can pass multiple FILTER functions to CALCULATE, so maybe try simething like this?
My Calc = CALCULATE(
SUM(something) ,
FILTER('Product Data', 'Product Data'[ProductCategory]="Mountain Bikes") ,
FILTER('Sales Data'[Unit Price]>1000)
)
First, thanks for your response:
I got this to work:
Road Bikes >1 = CALCULATE(
SUM('Sales Data'[Number sold]),
FILTER('Product Data', 'Product Data'[ProductCategory]="Road Bikes"),FILTER('Sales Data','Sales Data'[Number sold]>1))
I understand that CALCULATE is allowing this and that is awesome.
HOWEVER, I still don't understand why I cant write a FILTER statement (Using"New Table") on multiple tables without having to SUM, AVERAGE, etc. the data within CALCULATE.
What if I just want to FILTER the data exactly as it is in the first table, with some criteria from a second joined table without having to go the reports?
I feel like I am missing something here...The CALCUATE function is pretty handy and you can do some pretty powerful stuff with it if you need to.
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
Duplicate reply
Hi Phil_Seamark, in the code you have provided above I would like to know one thing.
There are two filters here. Which of the above will be first evaluated?
