cancel
Showing results for
Did you mean:
Helper I

## 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
Solution Sage

## 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
Highlighted
Solution Sage

## 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 !

Helper I

## 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?

Solution Sage

## 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 !

Helper I

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

Helper I

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

Helper I

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

Solution Sage

## 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

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors