cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Adding index number based on category

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Adding index number based on category

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?

Proud to be a Datanaut!

10 REPLIES 10
Super Contributor

## Re: Adding index number based on category

@Per-J

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

Proud to be a Datanaut!

Super User I

## Re: Adding index number based on category

@Per-J Please try this using "New Column"

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

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Frequent Visitor

## Re: Adding index number based on category

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?

Super Contributor

## Re: Adding index number based on category

@Per-J

could you post this dataset where you get dupes?

thanks

Proud to be a Datanaut!

Super User I

## Re: Adding index number based on category

@Per-J 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)`

Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !

Frequent Visitor

## Re: Adding index number based on category

@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.

Frequent Visitor

## Re: Adding index number based on category

@PattemManohar

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

Tricky.

Frequent Visitor

## Re: Adding index number based on category

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.

Super Contributor

## Re: Adding index number based on category

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?

Proud to be a Datanaut!

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors