Forum Discussion
how to filter using another column
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)
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.
6 Replies
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.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 regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicI 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 ?
