Forum Discussion

viajero03082's avatar
viajero03082
Regular Visitor
5 years ago
Solved

Data segmentation

Hello everyone šŸ˜€

Q= How can I extract and name the following Alloc?

I have a problem with the next data separation

 

I have to get the different alloc (alloc_2, alloc_3 ....) with the help of the id and dates

That is, an S1 id will Alloc_1, Alloc_2, Alloc_3, Alloc_4 because it has 4 dates

For alloc_1 it is easy to create a column that extracts the first date relative to the id

 

1st = CALCULATE(FIRSTDATE('Tabla2 (2)'[DATE]), ALLEXCEPT('Tabla2 (2)','Tabla2 (2)'[show_id]))

 

Then a Conditional to name

 

Alloc_1 = if('Tabla2 (2)'[DATE]=BLANK(),"0_ALLOC",if('Tabla2 (2)'[DATE]='Tabla2 (2)'[1st],"Alloc_1","Alloc_n"))

 

How can I extract and name the following Alloc?

 

  • Fowmy's avatar
    Fowmy
    5 years ago

    viajero03082 

    You can use RANKX to create the sequence. Use the code below for your column. T5 is my table name, you can replace it with yours.

    Allocation = 
IF(
    [DATE]=BLANK(),
    "0_ALLOC",
    RANKX( FILTER(T5,T5[show_id] = EARLIER(T5[show_id])) , T5[DATE] ,,ASC) & "_ALLOC"
)

     

     

     





2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    viajero03082 

    You can use RANKX to create the sequence. Use the code below for your column. T5 is my table name, you can replace it with yours.

    Allocation = 
IF(
    [DATE]=BLANK(),
    "0_ALLOC",
    RANKX( FILTER(T5,T5[show_id] = EARLIER(T5[show_id])) , T5[DATE] ,,ASC) & "_ALLOC"
)

     

     

     





  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    viajero03082 

     

    Such things should be done in Power Query. Not in DAX. I'd kindly suggest you use the right tool for the job. "The fact that something can be done does not mean it should be done"... to cite the long-standing adage.