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

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

MegaOctane1 Here are some suggested changes, not sure if will improve performance greatly. Any chance you can post sample data?

SAP_InvoicePartner_DIFFERENCES = 
VAR SAP=
    CALCULATETABLE(
        EXCEPT(
            DISTINCT('SAP Invoices'[SAPKEY]),
            DISTINCT('Partner Invoices'[KREDKEY])
            ),
            INTERSECT(DISTINCT('SAP Invoices'[CustomerNumber]),DISTINCT('Partner Invoices'[CustomerNumber]))
    )

VAR InvoicePartner=
    CALCULATETABLE(
        EXCEPT(
            DISTINCT('Partner Invoices'[KREDKEY]),
            DISTINCT('SAP Invoices'[SAPKEY])
            ),
            INTERSECT(DISTINCT('SAP Invoices'[CustomerNumber]),DISTINCT('Partner Invoices'[CustomerNumber]))
    )
RETURN

UNION (
GENERATE(
    SAP,
    SELECTCOLUMNS(
        'SAP Invoices',
        "CustomerNumber",[CustomerNumber],
        "InvoiceNumber",[InvoiceNumber],
        "Amount",[InvoiceAmount],
        "Kilde","SAP"
        )
    ),

GENERATE(
    InvoicePartner,
    SELECTCOLUMNS(
        'Partner Invoices',
        "CustomerNumber",[CustomerNumber],
        "InvoiceNumber",[InvoiceNumber],
        "Amount",([InvoiceAmount]),
        "Kilde","InvoicePartner"
        )
    )
)

 

MegaOctane1's avatar
MegaOctane1
Helper I
2 years ago

Thank you.

I will try to upload a pbix file later today. Just have to clean it up a little bit.