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

Filter table based on another table

Just want to ask something. I have 2 tables in Powe BI. One contains a data set of 13 million rows and the other table contains one columb with the list of values I want to filter in the main table. I'm trying to create a filtered table containing only all the values in my 2nd table. Is it possible to filter based on the whole list of my 2nd table?
7 REPLIES 7
nirvana_moksh Post Prodigy
Post Prodigy

Re: Filter table based on another table

Can you try this : Table = CALCULATETABLE('TABLE 1',EXCEPT(VALUES('TABLE 1'[ID]),VALUES(TABLE 2 [ID])))

nirvana_moksh Post Prodigy
Post Prodigy

Re: Filter table based on another table

FYI you can also do this at the Power Query level, by doing a Merge as New and using Inner Join 

justin22pogi
Regular Visitor

Re: Filter table based on another table

I'll try doing the calculate table. But why do we need to use EXCEPT?

For the Power Query using Merge as New and Inner Join, do you have a link on the guide on how to donit?

Thanks for the reply?
rajendran Resident Rockstar
Resident Rockstar

Re: Filter table based on another table

Go to  Query Editor - > Select Table 1->Home -> Combine->Merge Queries-> YOur Table1 will appear default, Select Table 2, Select the joining column, Select the join Type as inner join.

 

You should be able to get lots of example in internet. Check the 'Combine Data ' section in this link.  Its just straight forward.

 

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data

 

Thanks

Raj

Highlighted
nirvana_moksh Post Prodigy
Post Prodigy

Re: Filter table based on another table

Below screenshots should help you:

 

Merge 1.jpgClick on Edit Queries Which will take you to Power Query ModeMERGE 2.jpgHave your Table 1 and Table 2 in order and use the Join Condition as needed

joebc
Frequent Visitor

Re: Filter table based on another table

Is one of these methods faster or more efficent than the other?

CristianAv Resolver I
Resolver I

Re: Filter table based on another table

If you filter in PowerQuery, then you avoid loading all the "useless" date to the model.

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors