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 trick) to filter those columns for the same  date period and see what are the difference between those 2 tables ( invoice that are in the first table not contained on second

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

19 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

       it is the same date in this format

      dd/mm/yyyy

       

      I already did, a create a correlation with a calendar table created by me, now I need to  compare the invoice number column in the those different table, how I can do that?

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        i try to create this new column but i get an error

        Transfered =
        VAR Test =
        COUNTROWS (
        FILTER (
        InvoiceLineTESTv4,
        InvoiceLineTESTv4[Invoice.Nr] = ExtractTESTv4[Column4]
        )
        )
        RETURN
        IF ( Test = 1, "Yes", "No" )

         

        A single value for column 'Column4' in table 'ExtractTESTv4' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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