Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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.

 

DateCategoryIndex
15.03.2018Child1
12.04.2018Adult2
13.04.2018Adult3
02.04.2018Adult1
25.06.2018Child2
22.05.2018Adult4
27.09.2018Child4
22.08.2018Child3
15.10.2018Child5
02.10.2018Adult5



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

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?

 

 

 

 


 


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


Proud to be a Datanaut!  

View solution in original post

11 REPLIES 11
PattemManohar
Community Champion
Community Champion

@Anonymous Please try this using "New Column" 

 

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

image.png





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

Proud to be a PBI Community Champion




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

Anonymous
Not applicable

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

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




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

Proud to be a PBI Community Champion




Anonymous
Not applicable

@PattemManohar

 

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

Tricky. Smiley Frustrated

Anonymous
Not applicable

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.  

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?

 

 

 

 


 


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


Proud to be a Datanaut!  

Anonymous
Not applicable

@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 Smiley Happy

 

@Anonymous

 

could you post this dataset where you get dupes?

 

thanks

 


 


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


Proud to be a Datanaut!  

Anonymous
Not applicable

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

 

Both.PNGChildren.PNG

 

Adults.PNG

 

 

LivioLanzo
Solution Sage
Solution Sage

@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

 


 


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


Proud to be a Datanaut!  

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.