cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrewsk
Regular Visitor

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

1 ACCEPTED SOLUTION

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


Regards
Zubair

Please try my custom visuals

View solution in original post

12 REPLIES 12
BFL
New Member

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

BFL_0-1652258036745.png

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

BFL_1-1652258168247.png

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

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

 

BFL_2-1652258337123.png

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)

Phil_Seamark
Microsoft
Microsoft

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

How to change your solution into a switch statement  

Product Data'[ProductCategory]="Mountain Bikes"
Product Data'[ProductCategory]="Racing Bikes"
Product Data'[ProductCategory]="Tandem Bikes"

Duplicate reply

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.  


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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


Regards
Zubair

Please try my custom visuals

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

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

Just got it... 

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

Thank you for your reply. I have a final question if you don't mind. 

 

So in your example: 

New Table=
FILTER (
    'Product Data',
    'Product Data'[ProductCategory] = "Mountain Bikes"
        && CALCULATE ( AVERAGE ( 'Sales Data'[Unit Price] ) > 1000 )
)

 

When you are using AVERAGE, is it just to trick the code into using the FILTER context?

 

The actual data returned from this would be AVERAGED for each row, so it would not change. (IE the Average of a Unit Price of $1500.00 on a single row is still $1500.00. ) Smiley Happy

Hi @andrewsk

 

I believe this is how it works.

 

Assuming there is a one to many relationship between Product and Salesdata

 

1) The argument "CALCULATE ( AVERAGE ( 'Sales Data'[Unit Price] ) > 1000" is evaluated for each ROW of the PRODUCT Table.

 

2) Since we use CALCULATE INSIDE a ROW CONTEXT, it transforms that ROW context into a FILTER CONTEXT.

Meaning that... for each product it goes to SALESDATA table, fetches the ROWS that are related to that product and takes an average of those products

 

3) It that average is >1000, the Row of the Product Table is retained

else it is filtered out


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors