How to create a table and exclude certain row values?

[konradjonsson]

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?

[TomMartens]

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]

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 · Accepted solution]

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

[konradjonsson]

Yes, now it did solve the problem!

Brilliant - thanks a lot!

/Konrad

[HotChilli]

Create a table by filtering the first

TableX = FILTER(Table1, Table1[OrderCategory] = 2)

[konradjonsson]

Thank you.

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