Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
5 years ago
Solved

Giving Index Number

Hi The most powerful ad helper community 🙂   i need your kind supports to create a column.let me explain what i need pls :   I have table as below , wtih the  named columns : "material"  , "Orde...
Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    erhan_79 

    The solution I posted was for measures, not  new calculated columns in the data table.

    If you want to add these columns to the actual data table, you need:

    1) New column concatenating date and order number

     

    Concatenate date and order number = INT(INT('Table'[Delivery Date ]) & 'Table'[Order Number])

     

    2) New column for the index:

     

    Index =
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Material] = EARLIER ( 'Table'[Material] ) ),
    'Table'[Concatenate date and order number],
    ,
    ASC
)

     

    and you get this:

     

     

  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    erhan_79 

    Good point! You can solve it for example by "inflating" the date expression: INT(table[Date]) * 100000000000000 + table[order number]

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

erhan_79 

Here is one way. 

1) Create a measure concatenating date (converted into an integer) and order number, making the result an integer:

 

sort by =
INT ( INT ( MAX ( 'Table'[Delivery Date ] ) ) & [Sum order number] )

 

2) create the index using RANKX on this [sort by] measure:

 

Index column by Material =
RANKX (
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Material] ),
        NOT ( ISBLANK ( [sort by] ) )
    ),
    [sort by],
    ,
    ASC
)

 

and you get this result

  • erhan_79's avatar
    erhan_79
    Post Prodigy
    5 years ago

    Dear PaulDBrown ;

     

    thank you very much , 

     

    sort measure gave error so i coreccted as below , am i right ?

     

    sort by =
    INT ( INT ( MAX ( 'Table'[Delivery Date ] ) ) & SUM('Table'[Order Number] ))
     
    and after that i tried a create a new columnn but i gor below error ,could you pls check 
     

     

     
    i thik i try to create column but your example is measure , is there any way to create colum for ındex?
    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      5 years ago

      erhan_79 

      The solution I posted was for measures, not  new calculated columns in the data table.

      If you want to add these columns to the actual data table, you need:

      1) New column concatenating date and order number

       

      Concatenate date and order number = INT(INT('Table'[Delivery Date ]) & 'Table'[Order Number])

       

      2) New column for the index:

       

      Index =
RANKX (
    FILTER ( ALL ( 'Table' ), 'Table'[Material] = EARLIER ( 'Table'[Material] ) ),
    'Table'[Concatenate date and order number],
    ,
    ASC
)

       

      and you get this:

       

       

      • erhan_79's avatar
        erhan_79
        Post Prodigy
        5 years ago

        Thank you very much PaulDBrown , this is what i needed .