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 !

      • 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