Forum Discussion

MegaOctane1's avatar
MegaOctane1
Helper I
2 years ago
Solved

DAX code for comparing two tables, and showing the differences - Is it efficient enough?

Hello guys, I have two tables, with the columns:  CustomerNumber, InvoiceNumber, Amount, Key (the key column is "invoicenumber & amount") I compare these, and with DAX formula, i get a new table w...
"DAX"
Compare
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    MegaOctane1 Big help. So, why can't you just do this?

     

    SAP_InvoicePartner_DIFFERENCES 3 = 
    VAR __SAP = SELECTCOLUMNS('SAP Invoices', "CustomerNumber", [CustomerNumber], "InvoiceNumber", [InvoiceNumber], "Amount", [Amount])
    VAR __InvoicePartner = SELECTCOLUMNS('Partner Invoices', "CustomerNumber", [CustomerNumber], "InvoiceNumber", [InvoiceNumber], "Amount", [Amount])
    VAR __SAPExcept = EXCEPT(__SAP, __InvoicePartner)
    VAR __PartnerExcept = EXCEPT( __InvoicePartner, __SAP)
    VAR __Result = 
        UNION(
            ADDCOLUMNS( __SAPExcept, "Kilde", "SAP"),
            ADDCOLUMNS( __PartnerExcept, "Kilde", "InvoicePartner")
        )
RETURN
    __Result

     

MegaOctane1's avatar
MegaOctane1
Helper I
2 years ago

PBIX tile with sample data 
Let me know if it works. and if the data is clean 🙂

I would also like your help to create a mesure to count how many customers has difference in the balance. I have only one customer with zero difference. so the answer should be 4...

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

MegaOctane1 Big help. So, why can't you just do this?

 

SAP_InvoicePartner_DIFFERENCES 3 = 
    VAR __SAP = SELECTCOLUMNS('SAP Invoices', "CustomerNumber", [CustomerNumber], "InvoiceNumber", [InvoiceNumber], "Amount", [Amount])
    VAR __InvoicePartner = SELECTCOLUMNS('Partner Invoices', "CustomerNumber", [CustomerNumber], "InvoiceNumber", [InvoiceNumber], "Amount", [Amount])
    VAR __SAPExcept = EXCEPT(__SAP, __InvoicePartner)
    VAR __PartnerExcept = EXCEPT( __InvoicePartner, __SAP)
    VAR __Result = 
        UNION(
            ADDCOLUMNS( __SAPExcept, "Kilde", "SAP"),
            ADDCOLUMNS( __PartnerExcept, "Kilde", "InvoicePartner")
        )
RETURN
    __Result

 

  • MegaOctane1's avatar
    MegaOctane1
    Helper I
    2 years ago

    Nice, simple, neat - Perfect!!

    Thanks Greg_Deckler 

    One more question: How do i count how many customers doesn't have 0 balance when compared? So i can have this value in a card visual.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      2 years ago

      MegaOctane1 Well, you could do something like this (accounting for floating point inaccuracies):

      Count Non-Zero Balances = 
    VAR __Table = SUMMARIZE('CustomerMaster', [Name], "__Value", SUM('SAP Invoices'[Amount]) - SUM('Partner Invoices'[Amount]))
    VAR __Result = COUNTROWS(FILTER(__Table, [__Value] > .0001 || [__Value] < -.0001))
RETURN
    __Result