cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
raymonvt
Frequent Visitor

Filter data from column a with data in column b

I have 2 columns 1 (a)with a excensive list of office numbers and 1 (b) is a extensive list of office numbers that i dont want to use.

how can i filter column a with the data inside column b.

for examble

column a

10

11

12

13

14

15

16

 

column b

10

12

13

15

 

1 ACCEPTED SOLUTION

@raymonvt 

This code should do it

Table 2 = 
FILTER(
    Table1,
    NOT(Table1[Invoice No.] IN VALUES(t[Invoice No.]))
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

View solution in original post

4 REPLIES 4
Fowmy
Super User IV
Super User IV

@raymonvt 

Are they in they are in separate tables? Add a new table form Modeling Tab with the code below and adjust your table and column names 

Filtered Table = 
FILTER(
    Table1,
    Table1[ColumnA] IN VALUES(Table2[ColumnB])
)

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

raymonvt
Frequent Visitor

I think this is doing the exact oppositie form what i want so it might be usefull. How can I this table but instad of getting the data that is inside table2 columnB get all the data from table1 columnA that is NOT in table2 columnB. i saw someware that the <> operator is used for not but when i try 

 

Filtered Table = 
FILTER(
    Table1,
    Table1[ColumnA] <> VALUES(Table2[ColumnB])
)

it doesnt work and i get the folowing error: A table of multiple values was supplied where a single value was expected. 

@raymonvt 

This code should do it

Table 2 = 
FILTER(
    Table1,
    NOT(Table1[Invoice No.] IN VALUES(t[Invoice No.]))
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Blog - Data Visualization

Website   YouTube    LinkedIn   PBI User Group

View solution in original post

Greg_Deckler
Super User IV
Super User IV

@raymonvt - Perhaps:

Measure = 
  VAR __Table1 = SELECTCOLUMNS('Table","Column",[a])
  VAR __Table2 = SELECTCOLUMNS('Table","Column",[b])
RETURN
  COUNTROWS(EXCEPT(__Table1, __Table2))

Not very clear though. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors