Forum Discussion
comparing column row
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] ) ) )
so I've 2 different table
both tables contain a column called invoice nr,( the tables contain more column such as date and so on) my goal is to create a table to filter those columns for the same date period and see what are the difference between those 2 tables in the column amount -> if the amount is different for the same invoice, I need to display invoice number, amount in 1 table, amount in 2 table, and the different
what i could do it is to create a table, but i cant get data from 2 table and it is no work as should
CalculatedTable2 = VAR temp = CALCULATETABLE ( EXCEPT ( VALUES ( InvoiceLineTESTv4[Total in CZ] ), VALUES ( ExtractTESTv4[Total in CZ] ) ), INTERSECT ( VALUES ( InvoiceLineTESTv4[Invoice.Nr] ), VALUES ( ExtractTESTv4[Document No.] ) ) )
RETURN GENERATE ( temp, CALCULATETABLE ( SELECTCOLUMNS ( InvoiceLineTESTv4, "Date", [Invoice.dtDate], "Amount XSP", [Total in CZ],"Invoice nr", [Invoice.Nr])))
in the screenshot, you can see that the table so create give me a result even if the amount it is same
but looks that display also invoice with the same amount
I think to depend on the fact that in the 1 table some invoice are split as shown in the image below (very few of them)
and in the second table, there is just one line for this invoice ( the total amount are the same in both tables )
if it is possible to like this:
basically the sum of invoice number 0000 (table1) = invoice number 0000 (table 2)
Could anyone help?
Zubair_Muhammad maybe :)
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 ID Supervisro ID Validation Result WS2001 7345382 Valid WS2002 Invalid WS2003 WS2001 Valid WS2004 WS2004 Invalid
Expected Output??
https://drive.google.com/file/d/1zwKegBu_WuBwNgWGE73c2VICnKf2vq8I/view?usp=sharing
can you try this link
#
this is the image for explaining you better
https://drive.google.com/open?id=1tP2kmgCsrr1vN6YHKNvlIt67_0224Tnk
table 1 table 2 invoice nr date amount change rate invoice number date amount change rate 1000 01/01/2018 10 1 1000 01/01/2018 10 1 1001 02/01/2018 5 1 1001 02/01/2018 15 1 1001 02/01/2018 5 1 1002 03/01/2018 25 1 1001 02/01/2018 5 1 1002 03/01/2018 20 1 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