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

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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)
		 )

 

andrewsk's avatar
andrewsk
Regular Visitor
8 years ago

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...

 

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    The CALCUATE function is pretty handy and you can do some pretty powerful stuff with it if you need to.  

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      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