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 with the differences. 
Can somebody check if the code is "efficient"?

I cannot to it in Power Query because one of the tables are a api query which uses 30-50 minutes to run, and it can time out i was told. Either way, the code below works fast. 
not sure if it is efficient enough.

 

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

VAR InvoicePartner=
    CALCULATETABLE(
        EXCEPT(
            VALUES('Partner Invoices'[KREDKEY]),
            VALUES('SAP Invoices'[SAPKEY])
            ),
            INTERSECT(VALUES('SAP Invoices'[CustomerNumber]),VALUES('Partner Invoices'[CustomerNumber]))
    )
RETURN
UNION (
GENERATE(
    SAP,
    CALCULATETABLE(
        SELECTCOLUMNS(
            'SAP Invoices',
            "CustomerNumber",[CustomerNumber],
            "InvoiceNumber",[InvoiceNumber],
            "Amount",[InvoiceAmount],
            "Kilde","SAP"
            )
        )
    ),

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

 

  • 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

     

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    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

      Thank you.

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

    • MegaOctane1's avatar
      MegaOctane1
      Helper I

      How do i remove recent datasources from a PBIX file before sharing? 
      I am afraid of sharing recent datasources and credentials

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion

        MegaOctane1 Those are not part of the PBIX file. Those items are cached locally on your computer. I run Power BI Desktop on multiple systems. My recent data sources are not the same between them and if I open a PBIX file on another computer, I have to re-establish the credentials.