Compare two sets of columns on different tables by value

[Anonymous]

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

ID	From Date	To Date
1	31/10/17	31/12/18
2	31/10/17	11/11/19
3	31/10/17	30/11/17

Invoice

ID	From Date	To Date
1	31/10/17	31/12/18
2	31/10/17	11/11/18
3	31/10/17	30/11/17

Required Result from Measure

ID	Measure result
1	0
2	1
3	0

[VahidDM]

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:

* 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]

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

Snapshot of Data:

billing

ID	From Date	To Date
2200059579046	31/10/2017	31/12/2018
1400074274632	31/10/2017	11/11/2019
1000021442273	31/10/2017	30/11/2017
2000029185068	31/10/2017	31/12/2018
1400069563915	31/10/2017	11/11/2019
1400011377608	31/10/2017	30/11/2017
1600080701673	31/10/2017	31/12/2018
1800027178380	31/10/2017	11/11/2019
2000099323350	31/10/2017	30/11/2017
2000061451305	31/10/2017	31/12/2018
1500057337836	31/10/2017	11/11/2019
2300013730962	31/10/2017	30/11/2017
1100033102735	31/10/2017	31/12/2018

Invoice:

ID	From Date	To Date
2200059579046	31/10/2017	11/11/2018
1400074274632	31/10/2017	31/12/2018
1000021442273	31/10/2017	30/11/2017
2000029185068	31/10/2017	11/11/2018
1400069563915	31/10/2017	31/12/2018
1400011377608	31/10/2017	30/11/2017
1600080701673	31/10/2017	11/11/2018
1800027178380	31/10/2017	31/12/2018
2000099323350	31/10/2017	30/11/2017
2000061451305	31/10/2017	11/11/2018

[Anonymous]

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.

[VahidDM]

@Anonymous 

It works well on my computer:

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]

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]

Hi @Anonymous ,

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

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

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]

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:

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]

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 · Accepted solution]

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)