Adding index number based on category

[Anonymous]

Hello, folks!

How can I add an index number that runs based on category type stated in another column? See the example picture. This is what I want to achiecve. I'd like the index column to give a number-series to all categories seperatly. Essentialy I would then be able to sort on category and get a continous series running from 1 to "whatever-number".

Aditionally I would like the index series in each category to run according to date in another column.

Date	Category	Index
15.03.2018	Child	1
12.04.2018	Adult	2
13.04.2018	Adult	3
02.04.2018	Adult	1
25.06.2018	Child	2
22.05.2018	Adult	4
27.09.2018	Child	4
22.08.2018	Child	3
15.10.2018	Child	5
02.10.2018	Adult	5

Is there any way to do this? I found this this forum-post interesting, but I can't really get it to fit my goal exactly. However, maybe some of you understand how to use this info for my purpose.

 

Best:

- Per-J.H.

[PattemManohar]

@Anonymous Please try this using "New Column" 

Index = RANKX(FILTER(Test13Rank,Test13Rank[Category]=EARLIER(Test13Rank[Category])),Test13Rank[Date],,ASC,Dense)

[Leslie1015]

Thanks for your example. Very simple but it definitely gave solution to my problem!

[Anonymous]

@PattemManohar, @LivioLanzo

Both of the solutions mentioned here gave close to what I'm looking for. Almost there. I really liked the simplicity of this, last example.

There is one major problem, though. Whenever there is similar dates, this code returns the same index-value. I must have unique values for each row, even if there are the same dates. Its ok that observations on the same date have 4, 5 and 6 as values, as long as they are before the index number for a later date.

My new column gave me four #11 - values, as there are four observations on the same day. These values have to be 11, 12, 13 and 14.

Is there a neat way of getting this done?

[PattemManohar]

@Anonymous Ok, then just remove the DENSE from the Rank. So it will be...

Index = RANKX(FILTER(Test13Rank,Test13Rank[Category]=EARLIER(Test13Rank[Category])),Test13Rank[Date],,ASC)

[Anonymous]

@PattemManohar

Removing the "dense"-statement only yealds the same result as LivioLanzo. There is still identical index-number on the same dates.

Tricky.

[Anonymous]

Dear @PattemManohar @LivioLanzo
Thanks for helping with this issue. You are most helpful.

I came to think that maybe there is a better way of getting this job done. If there is a formula, measure (or some other function), that can do two operations in separat orders.

First: sort by date

Then: give assending index numbers based on (only) category

This way, date is not in the mix when doing the numbering task. Is this a way to go, or even possible?

Best:

- Per-J. H.  

[LivioLanzo · Accepted solution]

What if you add another column called FkDte with this formula

=Table1[Date]+RANDBETWEEN(1,1000) / 10000

and then add another columns with this formula

=CALCULATE(
COUNTROWS( Table1 ),
ALLEXCEPT( Table1, Table1[Category] ),
Table1[FkDte] < EARLIER( Table1[FkDte] )
) + 1

by the way, do you only have 2 columns in the original table (Date, Category), or do you have other columns that could avoid the usage of the FkDte column?

[Anonymous]

@LivioLanzo

You sir! Are a true legend!
Thank you very much. This did the trick very good.
I guess there is a microscopic chance that it can land on the same number even still, as its a random time (?). Not to worry though, since there is rarely more than five observations on the same day in the dataset.

To your question. Yes, I've got several tables and colums in the dataset (about 40 colums in this table).

Thanks to the both of you

[LivioLanzo]

@Anonymous

could you post this dataset where you get dupes?

thanks

[Anonymous]

@LivioLanzo

The example I've given is only tentative and the real dataset is approx. 10k rows with a lot of data and columns. It's classified info, so I'd rather give examples. Under I've posted my results using your formulas. As you can see, same dates gives same number in both cases. In stead of 3, 3, 3 and 7, 7, 7, I need, 3, 4, 5 and 7, 8, 9.

Hope this helps understanding the problem.

[LivioLanzo]

@Anonymous

You need to convert your date column to real dates and do it like this:

=
CALCULATE (
    COUNTROWS ( Table1 ),
    ALLEXCEPT ( Table1, Table1[Category] ),
    Table1[Date] < EARLIER ( Table1[Date] )
)
    + 1