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

#thank you so much Zubair_Muhammad

looks to work,

now the calculated table give me the result for all the column ( all the period)

but I need to be filtered by table calendar Testv4, how I can do that?

 

now I get as result the missing invoice number(I that is very good) but I would like to see the details of this invoice store in the table Invoice line testv4

 

in your example my question it is on the calculated table you got result invoice nr, but I would like have also other info as the date

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
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 !

  • 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

    @ 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

  • 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)