Comparing values from two data sources

[Anonymous]

Hi All

Appreciate someone helping me out in this scenario.

I’ve two data sets that contains similar information. One of them is called “Gift Register” and the other is called “Expense Register”.

I want to do comparison between the two data to identify the match as well as mismatch. The rules I have are as follows:

If an entry is available in “Gift Register” but NOT available in “Expense Register”, then a column called “Expense Register Status” will be Red.

Example for this the entry for “James William” on 15/10/2020

If an entry is available in “Expense Register” but NOT available in “Gift Register”, then a column called “Gift Register Status” will be Red.

Example for this the entry for “Jessica Edward” on 6/6/2020

If the entry exists in both registers and the “Date” and “Gift Receiver” are matching, then I want to highlight the “value” in red.

I’m attaching sample data along with sample output as excel.

Gift Register

Date	Gift Receiver	Gift Value	Sponsoring Company
3/11/2020	Jon Doe	15	Company A
5/5/2020	James William	30	Company B
13/4/2020	Steve Arin	30	Company A
15/10/2020	James William	10	Company A

Expense Register

Date	Gift Receiver	Gift Value	Sponsoring Company
3/11/2020	Jon Doe	15	Company A
5/5/2020	James William	20	Company B
13/4/2020	Steve Arin	30	Company A
6/6/2020	Jessica Edward	25	Company B

Desired Output

Date	Gift Receiver	Gift Register Value	Expense Register Value	Gift Register Status	Expense Register Status
3/11/2020	Jon Doe	15	15	GREEN	GREEN
5/5/2020	James William	20	30	GREEN	GREEN
13/4/2020	Steve Arin	30	30	GREEN	GREEN
15/10/2020	James William	10	10	GREEN	RED
6/6/2020	Jessica Edward	25	25	RED	GREEN

[Anonymous]

Thanks a lot @v-stephen-msft 

Is there a way that I can show in the table BOTH Gift Receiver from both tables?

So I will have column for "Gift Register Receiver" and "Expense Register Receiver"

The rest will be the same. 

Thanks a lot

Date	Gift Register Receiver	Expense Register Receiver	Gift Register Value	Expense Register Value	Gift Register Value	Expense Register Value

[v-stephen-msft]

Hi @Anonymous ,

For new questions, please reopen the post to ask, thank you.

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

Best Regards,

Stephen Tao

[v-stephen-msft]

Hi @Anonymous ,

The final effect is shown in the figure below, and I will explain the steps below.

1.Create a column in the Expense Register table.

Column = IF(NOT([Date] in VALUES('Gift Register'[Date])),1)

2.Create a column in the Gift Register table.

Column = IF(NOT([Date] in VALUES('Expense Register'[Date])),1)

3.Create a table by DAX.

Table = 
VAR tt =
    SELECTCOLUMNS (
        'Expense Register',
        "Expense Date", [Date],
        "Expense Gift Receiver", [Gift Receiver],
        "Expense Gift Value", [Gift Value]
    )
VAR tt2 =
    SELECTCOLUMNS (
        'Gift Register',
        "Gift Date", [Date],
        "Gift Gift Receiver", [Gift Receiver],
        "Gift Gift Value", [Gift Value]
    )
VAR tt3 =
    FILTER (
        CROSSJOIN ( tt, tt2 ),
        [Gift Date] = [Expense Date]
            && [Expense Gift Receiver] = [Gift Gift Receiver]
    )
VAR tt4 =
    SELECTCOLUMNS (
        tt3,
        "Date", [Expense Date],
        "Gift Receiver", [Expense Gift Receiver],
        "Gift Register Value", [Gift Gift Value],
        "Expense Register Value", [Expense Gift Value],
        "Gift Register Status", "Green",
        "Expense Register Status", "Green"
    )
VAR tt5 =
    FILTER (
        SELECTCOLUMNS (
            'Expense Register',
            "Date", IF ( [Column] = 1, [Date] ),
            "Gift Receiver", IF ( [Column] = 1, [Gift Receiver] ),
            "Gift Value", IF ( [Column] = 1, [Gift Value] )
        ),
        NOT ( ISBLANK ( [Date] ) )
    )
VAR tt6 =
    SELECTCOLUMNS (
        tt5,
        "Date", [Date],
        "Gift Receiver", [Gift Receiver],
        "Gift Register Value", [Gift Value],
        "Expense Register Value", [Gift Value],
        "Gift Register Status", "Red",
        "Expense Register Status", "Green"
    )
VAR tt7 =
    FILTER (
        SELECTCOLUMNS (
            'Gift Register',
            "Date", IF ( [Column] = 1, [Date] ),
            "Gift Receiver", IF ( [Column] = 1, [Gift Receiver] ),
            "Gift Value", IF ( [Column] = 1, [Gift Value] )
        ),
        NOT ( ISBLANK ( [Date] ) )
    )
VAR tt8 =
    SELECTCOLUMNS (
        tt7,
        "Date", [Date],
        "Gift Receiver", [Gift Receiver],
        "Gift Register Value", [Gift Value],
        "Expense Register Value", [Gift Value],
        "Gift Register Status", "Green",
        "Expense Register Status", "Red"
    )
RETURN
    UNION ( tt4, tt6, tt8 )

4.Create a measure.

Measure = IF(MAX('Table'[Gift Register Value])=MAX('Table'[Expense Register Value]),"Green","Red")

5.Use Conditional formatting under Format to set the colors.

You can check more details from here.

Best Regards,

Stephen Tao

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

[lbendlin]

Have you considered using the EXCEPT() and INTERSECT() DAX functions?

[Anonymous]

I'm not sure if this will work since EXCEPT() and INTERSECT() matches one column. I want to make sure several columns match.

For example: I need to check both date and Gift Receiver to confirm if there's match or not and compare. So, is my assumption right regarding the EXCEPT and INTERSECT?