Finding differences between tables

[wolfy_]

Hi,

I'm having some troubles to find if values between tables are correct.

I have two table, one for a sale is done online and other when the customer came to the shop and take the product that have buyed online.

I need to be "alerted" when the Customer number is different from the two tables.

I was tinking to create a colum whit true or false in the DESK SALE table to be alerted

I have created a relation between

SALE DB[Sale ID] one<->tomany DESK SALE[Sale ID]

SALE DB
[Sale ID]
[Customer number]

DESK SALE
[Sale ID]
[Customer number]

Is there a way to do this?

[wolfy_]

Hi,

Maybe when I write the problem I was not clear what solution I was looking for.

I want to have a column saying TRUE or FALSE in the DESK SALE table, so I can be alerted visually.

The ones that are in yellow should have false.

[Anonymous · Accepted solution]

Hi @wolfy_,

Maybe you can try to use below measure formula:

Tag =
VAR _currentID =
    SELECTEDVALUE ( 'Desk DB'[Sale ID] )
VAR list =
    CALCULATETABLE (
        VALUES ( 'Sale DB'[Customer Number] ),
        FILTER ( ALL ( 'Sale DB' ), 'Sale DB'[Sale ID] = _currentID )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Desk DB'[Customer Number] ) IN list, TRUE (), FALSE () )

If above not help,  can you please share us a pbix file for test? I will test and coding formula on it.

Regards,

Xiaoxin Sheng

[Anonymous]

Not sure if I understand your requirement correctly? Do you want to check if any customer exists in DESK SALE table but not in SALE DB?

If you are, try the following DAX.

Compare = CALCULATE(COUNTROWS('Sales-1'),FILTER('Sales-1','Sales-1'[Customer number]=EARLIER('Sales-2'[Customer number])))>0

[wolfy_]

Hi Jessica,

I want to check if in both tables the [Sale ID] and the correspondent [Customer number] are the same.

[Anonymous]

Modifed the DAX to

MatchedTwoColumn = CALCULATE(COUNTROWS('Sales-1'),FILTER('Sales-1',IF('Sales-1'[Customer number]='Sales-2'[Customer number],IF('Sales-1'[Sale ID]='Sales-2'[Sale ID],TRUE()))))>0

[wolfy_]

Hi,

It gave the folowing error when i tried to implement: 

 It was not possible to determine a unique value for the 'Sale ID' column in the 'Sales-1' table. This can happen when a measurement formula refers to a column that contains many values, without specifying an aggregation such as min, max, cont, or sound to get a single result.

[Anonymous]

HI @wolfy_,

I think you can try to use 'INTERSECT' function to get intersection records from two table, store them as variable table.

Then check current row content if it exist in variable table, return tag based on above result.

Reference link:

INTERSECT Function (DAX)

Regards,
Xiaoxin Sheng

[wolfy_]

HI,

INTERSECT Function (DAX) seems a good solution, but my tables SALE DB and DESK SALE have more columms than just the [Sale ID] and [Customer number].