topic Re: DAX code for comparing two tables, and showing the differences - Is it efficient enough? in DAX Commands and Tips

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

MegaOctane1 — Tue, 17 Oct 2023 14:58:45 GMT

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" ) ) ) )

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

Greg_Deckler — Tue, 17 Oct 2023 15:18:56 GMT

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" ) ) )

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

MegaOctane1 — Tue, 17 Oct 2023 15:26:09 GMT

Thank you.

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

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

MegaOctane1 — Tue, 17 Oct 2023 19:33:39 GMT

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

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

Greg_Deckler — Tue, 17 Oct 2023 19:50:21 GMT

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.

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

MegaOctane1 — Tue, 17 Oct 2023 20:08:17 GMT

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...

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

Greg_Deckler — Tue, 17 Oct 2023 20:38:40 GMT

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

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

MegaOctane1 — Wed, 18 Oct 2023 05:35:16 GMT

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.

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

Greg_Deckler — Wed, 18 Oct 2023 13:16:25 GMT

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

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

MegaOctane1 — Thu, 19 Oct 2023 08:50:48 GMT

It works!! Thanks Greg_Deckler