Forum Discussion

andrewsk's avatar
andrewsk
Regular Visitor
8 years ago
Solved

FILTER on multiple tables

Hi all. I am new to DAX and am having a hard time getting my head around the FILTER command.   I want to Filter Data based off of multiple tables (Properly joined via many to one relationships) Her...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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

HarsimranPWRBI's avatar
HarsimranPWRBI
Helper I
6 years ago

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

aleksvp's avatar
aleksvp
Helper II
5 years ago

Guys, is there a way to do this so the filters are treated like a logical OR, not a logical AND?

  • aleksvp's avatar
    aleksvp
    Helper II
    5 years ago

    Just got it... 

    CALCULATETABLE (
    'Product Data',
    FILTER (CROSSJOIN('Product Data', 'Sales Data'), 'Product Data'[ProductCategory] = "Mountain Bikes" || 'Sales Data'[Unit Price] > 1000 )
)