how to filter using another column

[mreig]

Ok so i have 2 columns, each one from a diferent table, in their tables they are called the same "division_id" when i select both columns to make a graph or a table, they appear with different names.

The table is something like this

Name	ID	divison_id SO	division_id Partner
Company 1	1	20	20
Company 2	2	20	15
Company 1	3	13	13
Company 1	4	10	10
Company 2	5	0	23

Ok, so what i need to do is, only show the rows where the "Divison_id" are not equal

something like this:

Name	ID	divison_id SO	division_id Partner
Company 2	2	20	15
Company 2	5	0	23

How do i do this pls help (sorry for bad english)

[Anonymous · Accepted solution]

Hi @mreig ,

I did it in three ways, please check.

• Method1: In Power Query:

Merge as new Queries --> Expand and remain only division_id column--> Add a custom column with a flag (1 or 0) --> Filter out Custom column =0:

The final output is shown below:

• Method2: Use DAX to add a new table:

As @Anonymous said, you could use LOOKUPVALUE() to create a new table like this:

New Table = 
var _t=ADDCOLUMNS('Sale_order',"division_id res", LOOKUPVALUE('Res_partner'[division_id],[Name],[Name],[ID],[ID]))
return FILTER(_t,[divison_id]<>[division_id res])

• Method3: Use DAX to create measures:

Measure = IF(MAX('Res_partner'[division_id])<>MAX('Sale_order'[divison_id]),1)

 Then apply the measure to filter pane, set as "is 1", the final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd]

Hey @mreig ,

add a new calculated column and compare if they are equal:

Check DivisionID = myTable[divison_id SO] = myTable[division_id Partner]

Then filter on "false" values and you got all the records where they are different.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[mreig]

I have this two tables, inside they have the "division_id" column.

When i select both and many other columns it appears a table.

Ok, so how do i creata a new calculated column here ?

[Anonymous]

Go to data view and click here

[mreig]

I know but this column is crated in one of the tables.

I cannot get the column from the other table.

[Anonymous]

You can try using Lookupvalue.