Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
MegaOctane1
Helper I
Helper I

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

 

1 ACCEPTED 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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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. 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

It works!! Thanks @Greg_Deckler 

Thank you.

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.