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]
            )
        )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Anonymous

 

Same date period means...same day or same month or same year?

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

       

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Anonymous

         

        HI,

         

        Please try this calculated table

        From the Modelling Tab>>>New Table

         

        CalculatedTable =
CALCULATETABLE (
    EXCEPT (
        VALUES ( InvoiceLineTESTv4[Invoice Nr] ),
        VALUES ( ExtractTESTv4[Invoice Nr] )
    ),
    INTERSECT ( VALUES ( InvoiceLineTESTv4[Date] ), VALUES ( ExtractTESTv4[Date] ) )
)