Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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"
)
)
)
)
Solved! Go to Solution.
@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 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"
)
)
)
How do i remove recent datasources from a PBIX file before sharing?
I am afraid of sharing recent datasources and credentials
@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.
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...
@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
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.
@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
Thank you.
I will try to upload a pbix file later today. Just have to clean it up a little bit.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
18 | |
15 | |
12 | |
11 | |
8 |
User | Count |
---|---|
24 | |
17 | |
11 | |
11 | |
10 |