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

Zubair_Muhammad

thank you so much !

Anonymous's avatar
Anonymous
Not applicable
8 years ago

@ Zubair_Muhammad

 

 

thank you for the solution you offered me,

 

if i wanna add compare the date the number invoice and the amount($) , how i can add the parameter amount the formula? 

in the calculated table now i dispplay date just from 1 table can i add the amount of the 2 table as well? how?

thank u

 

i try to 
CalculatedTable2 = VAR temp = CALCULATETABLE ( EXCEPT ( VALUES ( InvoiceLineTESTv4[Invoice.Nr] ), VALUES ( ExtractTESTv4[Document No.] ) ), INTERSECT ( VALUES ( InvoiceLineTESTv4[Total in CZ] ), VALUES ( ExtractTESTv4[Total in CZ] ) ) )
RETURN GENERATE ( temp, CALCULATETABLE ( SELECTCOLUMNS ( InvoiceLineTESTv4, "Date", [Invoice.dtDate], "Total XSPED", [Total in CZ],"Invoice Reference", [Invoice.Reference] ) ) ) 

 

 

what i want to compare invoice number to see if same invoice have same amount

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    HI Anonymous

     

    Try this calculated Table

    As I understand,, you want to add Amount from Table2 where Date and Invoice Number match..Right??

     

     

    CalculatedTable2 =
ADDCOLUMNS (
    InvoiceLineTESTv4,
    "Table2 Amount", CALCULATE (
        SUM ( ExtractTESTv4[Amount] ),
        FILTER (
            ExtractTESTv4,
            [Invoice Nr] = InvoiceLineTESTv4[Invoice Nr]
                && [Date] = InvoiceLineTESTv4[Date]
        )
    )
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    so this is the link to file I'm using, so it will be more clear

     

    https://www.dropbox.com/s/5crm2812zkkoekv/Test2.pbix?dl=0

     

    the part number 1 i used your code to check the invoice number and the date,  looks to work fine!

     

    on part 2 I need to check the invoice number and the amount ( sometime invoice on table one have a different amount of invoice in table 2)

     

    if one amount is different I need to display:

    invoice number, the amount in table 1, the amount in table 2 and the difference between them

     

    Zubair_Muhammad

     

    image link

    https://www.dropbox.com/s/mlpyc73bra6k33r/Capture.PNG?dl=0

     

     

    1 problem is that checking the data some invoice on table one is split in more line (the same invoice in table 2 is always in 1 line)

    so in case, the invoice is split in more line I need to check the some of this line with same invoice number = to invoice amounth in table 2 

     

    example of split invoice check invoice number 40001412 ( is on 3 lines) in table 1 (InvoiceLineTESTv4)