Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

comparing column row

so I've 2 different table both tables contains a column called invoice nr,( the tables contain more column such as date and so on) my goal is to create a measure (or something else that could do the...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    Try this revision

    Please see revised file as well

     

    CalculatedTable =
VAR temp =
    CALCULATETABLE (
        EXCEPT (
            VALUES ( InvoiceLineTESTv4[Invoice Nr] ),
            VALUES ( ExtractTESTv4[Invoice Nr] )
        ),
        INTERSECT ( VALUES ( InvoiceLineTESTv4[Date] ), VALUES ( ExtractTESTv4[Date] ) )
    )
RETURN
    GENERATE (
        temp,
        CALCULATETABLE (
            SELECTCOLUMNS (
                InvoiceLineTESTv4,
                "Date", [Date],
                "Other Column1", [Other Column.1]
            )
        )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

HI Anonymous

 

Could you copy paste few rows of from each Table with Expected Ouput

For some reason I am not able to access Drop Box

 

For example

 

Table1

 

Employee IDSupervisro IDValidation Result
WS20017345382Valid
WS2002 Invalid
WS2003WS2001Valid
WS2004WS2004Invalid


Expected Output

 

??

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago
    table 1    table 2   
             
    invoice nr  dateamountchange rate invoice numberdateamountchange rate
    100001/01/2018101 100001/01/2018101
    100102/01/201851 100102/01/2018151
    100102/01/201851 100203/01/2018251
    100102/01/201851     
    100203/01/2018201     
     1003 04/01/2018 30 1     

     

    so I need to check invoice number in both table and match that is the same amount for same invoice nr, if not should give as output:

    invoice number, the amount in 1 table, amount in 2 table, and the difference between the amount, and with a column for change rate from table 1 and 1 column with change rate from table 2.

     

    problem is that if you see on table 1 some invoice is split into multiple lines, in this case, the sum of the line with same invoice number in table 1 should be equal to same invoice nr amount in table 2.(always in one line)

     

    + in case that invoice number in table 1 is not present in table 2 than as output should have just the invoice nr and amount and change rate from table 1

     

    Zubair_Muhammad

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Anonymous

       

      So in above case what is the Final Output you desire?

      Can you put final output in Table form?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago
             
              
        output     
        Invoice number/amount table 1//change rate table 1//amount table 2//change rate 2//difference (amont table1-amount table 2)/
        10022012515
        1003301  30
              
              
             Total of all difference 35

         

        this would be the output

        Zubair_Muhammad