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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Compare two sets of columns on different tables by value

Greetings,

I'm struggling to find a solution to my problem. I have two tables outlined below, I want to create a measure that validates:

  • For each ID in each table.
  • That both the from and to dates for each table match
  • And then return a 0 - No mismatch or 1 - Mismatch (True or False)

I'm struggling to find a way to compare the two sets of dates columns for each ID and return a 0 or 1. The requirement is to return the rows in the invoice table if there is a mismatch in the billing. My thoughts are to create a measure and filter on the measure when there is a mismatch. Please note the data sources uses direct query so limited in what I can do.

 

Any help would be much appreciated

 

Tables

Billing

IDFrom DateTo Date
131/10/1731/12/18
231/10/1711/11/19
331/10/1730/11/17

 

Invoice

IDFrom DateTo Date
131/10/1731/12/18
231/10/1711/11/18
331/10/1730/11/17

 

Required Result from Measure

IDMeasure result
10
21
30
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Thank you for your help,

I've managed to solve it now. I created three new variables, one to retrieve the billing ID, another for the invoice ID and lastly one that returns the value of billing where there s a match to invoice and it worked 🙂

 

Measure =
VAR _billing_id = SELECTEDVALUE(billing[ID])
VAR _invoice_id = SELECTEDVALUE(invoice[ID])
VAR _curid = IF(_billing_id == _invoice_id, _billing_id, "")
VAR _billfdate =
CALCULATE (
MAX ( 'billing'[From Date] ),
FILTER ( 'billing', 'billing'[ID] = _curid)
)
VAR _billtdate =
CALCULATE (
MAX ( 'billing'[To Date] ),
FILTER ( 'billing', 'billing'[ID] = _curid)
)
VAR _invfdate =
CALCULATE (
MAX ( 'invoice'[From Date] ),
FILTER ( 'invoice', 'invoice'[ID] = _curid)
)
VAR _invtdate =
CALCULATE (
MAX ( 'invoice'[To Date] ),
FILTER ( 'invoice', 'invoice'[ID] = _curid)
)
RETURN
IF ( _billfdate = _invfdate && _billtdate = _invtdate , 0, 1)
 
ra67052_0-1631602864837.png

 

View solution in original post

9 REPLIES 9
VahidDM
Super User
Super User

Hi @Anonymous 

 

try this code:

Measure1 =
VAR _BFD =
    MAX ( Billing[From Date] )
VAR _BTD =
    MAX ( Billing[To Date] )
VAR _IFD =
    LOOKUPVALUE ( Invoice[From Date], Invoice[ID], MAX ( Billing[ID] ) )
VAR _ITD =
    LOOKUPVALUE ( Invoice[To Date], Invoice[ID], MAX ( Billing[ID] ) )
RETURN
    IF ( _BFD = _IFD && _BTD = _ITD, 0, 1 )

 

Output:

VahidDM_0-1631178841666.png

* add ID from the Billing table to your table.

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos✌️!!

 

 

Anonymous
Not applicable

Thanks for you reply,

This has been helpful and have tried it but having tried the following on my data:

 

Measure1 =
VAR _BFD =
MAX ( billing[From Date] )
VAR _BTD =
MAX ( billing[To Date] )
VAR _IFD =
LOOKUPVALUE ( invoice[From Date], invoice[ID], MAX ( billing[ID] ) )
VAR _ITD =
LOOKUPVALUE ( invoice[To Date], invoice[ID], MAX ( billing[ID] ) )
RETURN
IF ( _BFD = _IFD && _BTD = _ITD, 0, 1 )
 
I'm getting this error
ra67052_0-1631229202739.png

 

Snapshot of Data:
billing
IDFrom DateTo Date
220005957904631/10/201731/12/2018
140007427463231/10/201711/11/2019
100002144227331/10/201730/11/2017
200002918506831/10/201731/12/2018
140006956391531/10/201711/11/2019
140001137760831/10/201730/11/2017
160008070167331/10/201731/12/2018
180002717838031/10/201711/11/2019
200009932335031/10/201730/11/2017
200006145130531/10/201731/12/2018
150005733783631/10/201711/11/2019
230001373096231/10/201730/11/2017
110003310273531/10/201731/12/2018

 

Invoice:

IDFrom DateTo Date
220005957904631/10/201711/11/2018
140007427463231/10/201731/12/2018
100002144227331/10/201730/11/2017
200002918506831/10/201711/11/2018
140006956391531/10/201731/12/2018
140001137760831/10/201730/11/2017
160008070167331/10/201711/11/2018
180002717838031/10/201731/12/2018
200009932335031/10/201730/11/2017
200006145130531/10/201711/11/2018
Anonymous
Not applicable

Not sure what's going on and have spent a couple of hours unpicking it and am not sure why but it doe not seem to work.

@Anonymous 

 

It works well on my computer:

VahidDM_0-1631243988516.png

Can you share your power bi file here after removing all sensitive data?

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

 

Anonymous
Not applicable

Thanks @VahidDM

I've dropped it here:

https://drive.google.com/file/d/1mT3HjoFkZPURJNg8H2ArP24PjmntaT_u/view?usp=sharing

 

Not sure what's going on 😞

Anonymous
Not applicable

Hi @Anonymous ,

I tried to download your shared file, but I have no access to it...

yingyinr_1-1631517039163.png

I created a sample pbix file(see attachment) base on your sample data, please check whether that is what you want.

1. Create a measure to get the flag to judge the match or not

Measure = 
VAR _curid =
    SELECTEDVALUE ( 'billing'[ID] )
VAR _invfdate =
    CALCULATE (
        MAX ( 'invoice'[From Date] ),
        FILTER ( 'invoice', 'invoice'[ID] = _curid )
    )
VAR _invtdate =
    CALCULATE (
        MAX ( 'invoice'[To Date] ),
        FILTER ( 'invoice', 'invoice'[ID] = _curid )
    )
VAR _billfdate =
    CALCULATE (
        MAX ( 'billing'[From Date] ),
        FILTER ( 'billing', 'billing'[ID] = _curid )
    )
VAR _billtdate =
    CALCULATE (
        MAX ( 'billing'[To Date] ),
        FILTER ( 'billing', 'billing'[ID] = _curid )
    )
RETURN
    IF ( _billfdate = _invfdate && _billtdate = _invtdate, 0, 1 )

2. Apply the measure in visual/page/report level filter

yingyinr_0-1631516814633.png

If the above one still not working, could you please grant me the proper access to your shared file? Then I can check your file and provide a suitable solution. Thank you.

Best Regards

Anonymous
Not applicable

Thanks @Anonymous,

I've tried the method you have given me but still facing some issues. I have attached the file with full data in the link below. 

 

I'm really struggling with DAX, the billing table is the master table, only IDs on here is valid. I'm creating a third table Date Mismatch to highlight from the invoice table where the to and from dates not do not match the billing, this is to flag that the invoice is wrong as the master data is from the billing.

 

However, solutions thus far are only pulling back the mismatches in IDs and the dates. Looking at my three tables below:

ra67052_1-1631547890794.png

 

 

The first 11 rows in the portfolio and invoice table should show on the date mismatch table as a 1 in the measure which it is but it's also pulling back IDs not existing in the billing table. How do I filter out when the ID does not exist in the billing table but does in the invoice? 

 

I've tried many things and spent half a day already trying different measures and var's but it's not working. 

 

Here's the link to the file:

https://www.dropbox.com/s/py1pvcswh673brk/Compare%20two%20sets%20of%20columns%20on%20different%20tables%20by%20value.pbix?dl=0

 

Any help would be greatly appreciated 🙏, as I'm losing the will to live with this 😒

Anonymous
Not applicable

Sorry and just to add, the intent is to use the measure to filter only records where the dates from the billing table do not match the invoice table

Anonymous
Not applicable

Thank you for your help,

I've managed to solve it now. I created three new variables, one to retrieve the billing ID, another for the invoice ID and lastly one that returns the value of billing where there s a match to invoice and it worked 🙂

 

Measure =
VAR _billing_id = SELECTEDVALUE(billing[ID])
VAR _invoice_id = SELECTEDVALUE(invoice[ID])
VAR _curid = IF(_billing_id == _invoice_id, _billing_id, "")
VAR _billfdate =
CALCULATE (
MAX ( 'billing'[From Date] ),
FILTER ( 'billing', 'billing'[ID] = _curid)
)
VAR _billtdate =
CALCULATE (
MAX ( 'billing'[To Date] ),
FILTER ( 'billing', 'billing'[ID] = _curid)
)
VAR _invfdate =
CALCULATE (
MAX ( 'invoice'[From Date] ),
FILTER ( 'invoice', 'invoice'[ID] = _curid)
)
VAR _invtdate =
CALCULATE (
MAX ( 'invoice'[To Date] ),
FILTER ( 'invoice', 'invoice'[ID] = _curid)
)
RETURN
IF ( _billfdate = _invfdate && _billtdate = _invtdate , 0, 1)
 
ra67052_0-1631602864837.png

 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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