Forum Discussion
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] )
) + 1by 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?
Community ChampionAnonymous Please try this using "New Column"
Index = RANKX(FILTER(Test13Rank,Test13Rank[Category]=EARLIER(Test13Rank[Category])),Test13Rank[Date],,ASC,Dense)
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)
Removing the "dense"-statement only yealds the same result as LivioLanzo. There is still identical index-number on the same dates.
Tricky. :smileyfrustrated:
- LivioLanzo
Solution Sage
Anonymous
could you post this dataset where you get dupes?
thanks
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.
Thanks for your example. Very simple but it definitely gave solution to my problem!
