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

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

      • konradjonsson's avatar
        konradjonsson
        Helper II
        7 years ago

        Yes, now it did solve the problem!

         

        Brilliant - thanks a lot!

         

        /Konrad