Forum Discussion

konradjonsson's avatar
konradjonsson
Helper II
7 years ago
Solved

How to create a table and exclude certain row values?

I want to create a new table that include a) all SERIAL NUMBERS (unique) and b) the DATE when the serial number was first invoiced.

(this is to later on cross-reference this date with all subsequent transactions on the same serial number, e.g. spare part sales or warranties)

 

I have created a table, using a DAX-function:

SerialSoldDate = SUMMARIZE('OrderDetails';'OrderDetails'[SerialNumber];'OrderDetails'[InvoiceDate])

The result is a table that gives me all invoice dates for the specific serial number. I only want the initial sale (invoice) date.

 

There is a field called 'OrderDetails'[OrderCategory], where the value ‘2’ indicates that the transaction is the initial sale of the serial number.

I tried to use logical functions to refine the DAX function, but I could not get it to work.

 

EXAMPLE:

Origin data table

Serial Number             Invoice Date              OrderCategory

123456                   2019-05-14               2

123456                   2019-06-03               6

654444                   2019-02-17               2

654444                   2019-05-19               3

 

NEW TABLE (SerialSoldDate)

Serial Number             Sold Date

123456                   2019-05-14

654444                   2019-02-17

 

THE QUESTION

How can I create a table that includes only the invoice date of the initial sale, and not spare part sale or warranties?

Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    Hey,

     

    I do not fully understand ,,, but nevertheless maybe a simple DISTINCT around the SELECTCOLUMNS from my 1st answer is all you need, to bend the table to your will, like so:

    DISTINCT(
    SELECTCOLUMNS(
    ....
    )
)

    Regards,

    Tom

6 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    7 years ago

    Create a table by filtering the first

    TableX = FILTER(Table1, Table1[OrderCategory] = 2)
    • konradjonsson's avatar
      konradjonsson
      Helper II
      7 years ago

      Thank you.

      However not what I was looking for. Perhaps my description was bit vague.

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    Hey,

     

    this DAX statetement creates a table that just contains two columns the serial number and the invoice date. The DAX function FILTER is used to filter the rows where the Order Category equals 2.

    Initial Sales = 
SELECTCOLUMNS(
    FILTER('Table1'
        ,'Table1'[OrderCategory] = 2
    )
    , "Serial Number" , [Serial Number]
    , "Invoice Date" , [Invoice Date]
)

    You can create tables using DAX from the Menu: Modeling --> Ribbons: Calculations --> New Table

     

    Please be aware that calculated tables will be only recalculated on data refresh, and not if a user changes a slicer selection.

     

    Hopefully this is what you are looking for.

     

    Regards,

    Tom

     

    • konradjonsson's avatar
      konradjonsson
      Helper II
      7 years ago

      Thanks Tom!

      It is definitely one step in the right direction. There is one more requirement that I did not specify clearly in my initial question - there can be only one row per serial number, i.e. the table should have no duplicates.

       

      The reason that the current table have duplicates is that the original table include several rows with the same serial number (these rows are different order line items).

       

      So, I would need an addition to the DAX-function that only keeps one row per unique serial number.

       

      • TomMartens's avatar
        TomMartens
        Super User
        7 years ago

        Hey,

         

        I do not fully understand ,,, but nevertheless maybe a simple DISTINCT around the SELECTCOLUMNS from my 1st answer is all you need, to bend the table to your will, like so:

        DISTINCT(
    SELECTCOLUMNS(
    ....
    )
)

        Regards,

        Tom