Forum Discussion

Gurpreetsingh1's avatar
Gurpreetsingh1
Helper I
1 year ago
Solved

Comparing Two Sales Tables

Hi Guys, Hope you are doing well. I am working on a report in this report I have two  sales tables one is from SAP and Another is from Salesforce. And I have find match mismatch between both of them...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    Hi Gurpreetsingh1 ,

    Thank you for reaching out to the fabric community.  We have reviewed your scenario and implemented the logic to classify the records based on Product Code and Amount comparisons between the Salesforce and SAP tables.

    FYI:

     

    Based on our testing, the logic is working as expected. Please refer to the  Match Type column in the result to view the classification. 

     

    For better understanding, I’ve attached the PBIX file. Kindly go through it, it demonstrates the logic and output based on your scenario.

     

    Thank you for your response, m4ni & Deku .

     

    If my message helped solve your issue, please mark it as Accepted Solution. If it was helpful, consider giving it a Kudos.

     

     

     

m4ni's avatar
m4ni
Resolver I
1 year ago

hi Gurpreetsingh1 

 

There are always different ways to achieve this.  I prefer the DAX soution as its easier for me at least.

If you have the composite key, keep that.

Create a table in DAX as the following:

 

Compare =
VAR Table1 = SUMMARIZECOLUMNS(Table1[Key])
VAR Table2 = SUMMARIZECOLUMNS(Table2[Key])
RETURN INTERSECT(Table1, Table2)
 
This will give you eveything which matches between the two tables.  
For the unmatched rows, you need to decide whch table is the primary and secondary for the comparison.
If table 1 is the primary or master then use this code:
 
Compare =
VAR Table1 = SUMMARIZECOLUMNS(Table1[Key])
VAR Table2 = SUMMARIZECOLUMNS(Table2[Key])
RETURN EXCEPT(Table1, Table2)
 
otherwise switch the tables around in the EXCEPT.
This will give you eveything in the first table which is NOT in the second table.
 
Hope that helps.  Please remember to mark as solved if so.
 
 

 

  • Gurpreetsingh1's avatar
    Gurpreetsingh1
    Helper I
    1 year ago

    Hi m4ni Thanks for the help, but i need both the matched & unmatched orders in same table along with other columns.

    Eg Desired Result 

    Sales Order Product Code (SAP) Amount (SAP) Product Code (SF) Amount (SF) Match Type

     

    SO101PRD0015,000PRD0015,000✅ Full Match
    SO101PRD0023,200PRD0083,000❌ Product Code Mismatch
    SO102PRD0032,500PRD0092,400❌ Product Code Mismatch
    SO103PRD0044,000PRD0044,100⚠️ Amount Mismatch
    SO104PRD0051,800——❌ Missing in SF
    SO104PRD0062,200PRD0062,200✅ Full Match
    SO105——PRD0072,800❌ Missing in SAP



    • V-yubandi-msft's avatar
      V-yubandi-msft
      Community Support
      1 year ago

      Hi Gurpreetsingh1 ,

      Thank you for reaching out to the fabric community.  We have reviewed your scenario and implemented the logic to classify the records based on Product Code and Amount comparisons between the Salesforce and SAP tables.

      FYI:

       

      Based on our testing, the logic is working as expected. Please refer to the  Match Type column in the result to view the classification. 

       

      For better understanding, I’ve attached the PBIX file. Kindly go through it, it demonstrates the logic and output based on your scenario.

       

      Thank you for your response, m4ni & Deku .

       

      If my message helped solve your issue, please mark it as Accepted Solution. If it was helpful, consider giving it a Kudos.