Comparing tables

[Anonymous]

Hello All,

I have a model as shown and am trying to get some  discrepency out here.
The Buyers and Supplier is connected through the Buyer Number
The Supplier and Orders are connected through the Supplier Number
Ideally the Buyer should be connected to a Supplier in the Supplier Table and have a Order in the Orders Table.
But now we have some Orders that are placed by Buyers that have no connection in the Supplier Table.

To quaote an example.

Buyer A for has no row in Supplier table but has Orders in the Orders Table.
Buyer B is connected to Supplier Number 1 and 2 but in the Orders table he has Order on Supplier 3 as well.

Now i want to show somehow the Buyers who have Orders on a Supplier but that connection is not in the Supplier Table.

So maybe a column or something in the Orders table that says a yes or No for rows in the Orders table for which the Buyer and supplier is not there in the Supplier table.

Thanks

[MikeJohnsonZA]

Hi @Anonymous 

We can solve this problem with a single measure as both scenarios look to report the number of orders where the 'orders'[Buyer Number] does not equal 'Buyer'[Buyer Number], The measure below will do that, you can then add a table with the order number, this measure as well as the 'orders'[Buyer Number] and  'Buyer'[Buyer Number] 

Buyer Mismatches =
SUMX (
    'Orders',
    IF (
        RELATED ( 'Buyer'[Buyer number] ) = 'Orders'[Buyer Number],
        BLANK (),
        1
    )
)

In my example below, order 6 matches your first scenario and order 5 matches the second scenario.

[Anonymous]

@MikeJohnsonZA  Thanks a lot.I tried and it worked but now i get another issue with data.

I have Buyers C and D.C is linked to Supplier 5 in the Supplier table but we have Order with D and this does not show up.

Thanks

[MikeJohnsonZA · Accepted solution]

I've simulated that scenario in order 10 and I do get the buyer mismatch, can you share a screenshot.

[Anonymous]

@MikeJohnsonZA  Thanks. my mistake i was filtering with the wrong buyer.I just had a slicer wih the Buyer and was selecting the wrong one.But now it seems i go into more trouble.

The discrepency in data becomes more visible.I have Orders on a Buyer E but in the Suppliers table the supplier for example Supplier  6 is connected to Buyer F who is not there in the Buyer table.It returns a blank row in the Buyer name and number.Thanks a lot it serves my purpose.

Is there a way i could put a column in the Order table for Mismatch and put it to Yes or No depending on if i find a matching combination of Supplier and Buyer Number in Order table and Supplier table? 

[MikeJohnsonZA]

Sure

You would create a calculated column in the Orders table then use something like

Buyer is Mismatched = if([buyer Mismatched] = 1,"Yes","No")

Just a general warning on that, try not to create too many calculated columns in your model, they make the model larger and slow down your refreshes.