Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Comparing tables

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 ...
Need Help
  • MikeJohnsonZA's avatar
    MikeJohnsonZA
    5 years ago

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

     

MikeJohnsonZA's avatar
MikeJohnsonZA
Responsive Resident
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      MikeJohnsonZA
      Responsive Resident
      5 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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?