Data segmentation (indicators)

[Syndicate_Admin]

Hello everyone 🙂

Q How can I extract and name the next Alloc?

I need to know the number of crazy ones in 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]))

Then I got the first date 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 id and dates

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

As you'll notice there may be the same dates for an order and the dates aren't always consecutive

The ultimate goal is to filter the report by this alloc number 2, 3, 4 with the help of how many dates the specific ID has

Here's another example

We have separate dates and multiple IDs with dates in common and this should have Alloc_1, Alloc_2, Alloc_3, Alloc_4 since it is 4 dates where we try to address that ID

I hope you can help me, I would thank you very much

[MFelix · Accepted solution]

Hi @viajero03082 ,

You can do this on the query editor by doing the following steps:

• Group rows by show_id
  • Select the option all rows

• Add a new column with the syntax below:

Table.AddIndexColumn ([Count], "Index",1)

• Expand the new column created:

• Add a new custom column with the following syntax:

"Alloc_" & Number.ToText ([Index])

• Delete the Count column and the Index column:

If you want to do it in dax use the following code:

Alloc =
VAR temp_table =
    FILTER (
        ALL ( 'Table (2)'[show_id], 'Table (2)'[date] ),
        'Table (2)'[show_id] = EARLIER ( 'Table (2)'[show_id] )
            && 'Table (2)'[date] <= EARLIER ( 'Table (2)'[date] )
    )
RETURN
    "Alloc_" & COUNTROWS ( temp_table )

See PBIX file with two versions attach.