Forum Discussion

wolfy_'s avatar
wolfy_
Helper I
8 years ago
Solved

Finding differences between tables

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?

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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_'s avatar
      wolfy_
      Helper I
      8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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_'s avatar
      wolfy_
      Helper I
      8 years ago

      Hi Jessica,

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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_'s avatar
    wolfy_
    Helper I
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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