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

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,

         

        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.