Forum Discussion
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
Anonymous
This code should do itTable 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 🙂
4 Replies
Anonymous - 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.Anonymous
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 namesFiltered 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 🙂
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.
Anonymous
This code should do itTable 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 🙂
