Forum Discussion

Icon for Helper IV rank

Helper IV

5 years ago

Solved

search equivalence

Hello Everybody,

i would like to know if the exact values from a column in a table exist in a column from an other table.

1rst table : wyz_reporting supplier_invoices

2nd table : wyz_reporint bi_order

The solution is to know which data from the supplier_invoice table does not exist in the bi_order table.

Thanks for your help

Have a nice day

Tutorial Requests

v-yingjl
5 years ago
Hi Sylvine_Wyz ,

You can try to create this calculated column:

Flag = IF ( 'wyz_reporint bi_order'[Liaison] IN DISTINCT ( 'wyz_reporting supplier_invoices'[liaison] ), "yes", "no" )

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
search equivalence.pbix26 KB

7 Replies

FrankAT
Community Champion
5 years ago
Hi Sylvine_Wyz

you can use Powert query and merge both table with an anti-join. Take a look at the following link:

https://www.youtube.com/watch?v=9dG63ifH7Pk

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
- Sylvine_Wyz
  Helper IV
  5 years ago
  Hello FrankAT
  
  Thanks for your help.
  But i don't arrive. My 2 columns are not in POWER QUERY.
  They are 2 columns i created
  liaison = 'wyz_reporting supplier_invoices'[Frn Order N°]&" "&'wyz_reporting supplier_invoices'[ean]&" "&'wyz_reporting supplier_invoices'[supplier_id]
  
  i want to create a new column with yes or no if the values in the table 1 are in the table 2
v-yingjl
Community Support
5 years ago
Hi Sylvine_Wyz ,

You can try to create this calculated column:

Flag = IF ( 'wyz_reporint bi_order'[Liaison] IN DISTINCT ( 'wyz_reporting supplier_invoices'[liaison] ), "yes", "no" )

Attached a sample file in the below, hopes to help you.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
search equivalence.pbix26 KB
- Sylvine_Wyz
  Helper IV
  5 years ago
  Hi v-yingjl
  
  i tried your solution but there is an error.
  I can't to choice the column Liaison in the formul.
  
  😞
  - v-yingjl
    Community Support
    5 years ago
    Hi Sylvine_Wyz ,
    
    Seems like you have created a mesure instead of calculated column by this formula. This formula is used to create a calculated column in the wyz_reporint bi_order table. Try to create a new column not a new measure.
    
    Best Regards,
    Yingjie Li
    
    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.