Forum Discussion
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_DecklerCommunity 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" ) ) )- MegaOctane1Helper I
Thank you.
I will try to upload a pbix file later today. Just have to clean it up a little bit.
- MegaOctane1Helper I
How do i remove recent datasources from a PBIX file before sharing?
I am afraid of sharing recent datasources and credentials- Greg_DecklerCommunity 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.