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) Here is an example.

 

This works fine:

FILTER(

'Product Data', 'Product Data'[ProductCategory]="Mountain Bikes"

 

When I add another Table it breaks:

FILTER(

'Product Data', 'Product Data'[ProductCategory]="Mountain Bikes"

&& 'Sales Data'[Unit Price]>1000)) 

 

The error message I get is that "'Sales Data' cannot be found or may not be used in this expression"

 

My guess is that because FILTER only returns the defined Table, adding in another table messes it up, so I probably have to wrap the second parameter in something...

 

 

Thankyou in advance!!

  • 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

12 Replies

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Team, Please help me with my question

    If I have enter the CF number in Above then All three table can be filter together

    IF above enter the CF Number then 1st Table is working fine but I want all those rows should be display on 2nd table which come under the CF numbers

    Then if I  will select the any row  in Phase Plan table then

    below table resepective rows can be show in below 3rd table.

     

    if use did not enter any value in CF Number in Tab visual then below two tables Should be blank

    Summarized:

    User; Enter the CF number 

    1st table can show reseptive CF number

    2nd Table can show respective rows which come under the CF number

    3rd Table can show respetive rows which come under or Selection row only.

    if Visual CF number is blank then 2nd and 3rd table should be blank (defualt is blank 2nd 3rd)