Data segmentation (indicators)

[viajero03082]

Hello everyone

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

I need to know the number of allocations of these IDs, I extracted the first date from the date column.

1st = CALCULATE(FIRSTDATE('Tabla2 (2)'[Fecha de Alocación]), ALLEXCEPT('Tabla2 (2)','Tabla2 (2)'[No orden]))

After I got the first allocation with the name Alloc_1 and the other Alloc_n

Alloc_1 = if('Tabla2 (2)'[Fecha de Alocación]=BLANK(),"0_ALLOC",if('Tabla2 (2)'[Fecha de Alocación]='Tabla2 (2)'[1st],"Alloc_1","Alloc_n"))

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

That is, an ID of S1 will be Alloc_1, Alloc_2, Alloc_3 because it has 3 different dates ⬇️

As you will notice, there may be the same dates for an order and the dates are not always consecutive.

The final objective is to filter the report by this number of Alloc 2, 3, 4 with the help of how many dates the specific ID has.

Here is another example

We have separate dates and several IDs with dates in common and this should have Alloc_1, Alloc_2, Alloc_3, Alloc_4 since there are 4 dates where we try to attend that ID

I hope you can help me, I would appreciate it very much

[v-janeyg-msft · Accepted solution]

Hi, @viajero03082 

According to your description, I create a calculated colum to sort first, then create a measure or column to display results. Considering that you said there are duplicate date and id columns, but I don't know your data, so I sorted in pq and added the index column, so that there will be no less data when displayed in the visual.

Like this:

Column = RANKX(FILTER('table',[id]=EARLIER('table'[id])),'table'[date],,ASC,Dense)

Column 2 = CONCATENATE("Alloc_",'table'[Column])

If you still need help, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.