Creating an Index column based on a calculated column and a regular column

[RH10]

Hello everyone,

I would like to create an index column based on two other columns in my data, but I'm having trouble doing so. I have found a number of articles and videos on this topic which suggest using grouping in Power Query to accomplish this, but one of the columns that I want to use is a calculated column, so it doesn't show up in Power Query. An example of the data that I'm working with is as follows:

Course name	Date	Year (Calculated)	Index Number that I want
Chemistry	4/8/2022	2022	1
Chemistry	8/9/2022	2022	2
Biology	10/5/2022	2022	1
Biology	12/15/2022	2022	2
Biology	2/26/2023	2023	1
Math	7/4/2022	2022	1
Math	9/8/2022	2022	2
Math	2/5/2023	2023	1
Math	4/7/2023	2023	2
Math	8/5/2023	2023	3

Essentially the dataset that I imported into Power BI has the course names and the respective dates. I used DAX to calculate the year column from the date column. Now I want to create an index column based on the course names column and the calculated year column. For example, when a certain row has Math as the course and the year is 2022, the index will be 1, and it'll continue counting until the year changes to 2023, at which point it will reset back to 1. Please help me accomplish this in Power BI.

Thank you!

[serpiva64]

Hi,

here a solution in power query 

to obtain it you have :

- first to create the year column 

- then group by

- add a index column in AllRows

- maintain only this last created column

- expand all

and finally remove unnecessary columns

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[RH10]

Hello @serpiva64  ,

Thank you for your solution. Unfortunately, it does not work properly. My actual data is somewhat messy, as this project that I'm working on is supposed to be updated throughout the year. As of now, there are courses that don't have a specific date and are left blank, or just has a month, or just has the year. I apologize for not making this clear earlier. I actually tried to use the Power Query editor to create the Year column, but I couldn't figure out how to get it work (I'm still new to the Power BI system). Anyway, since the date column is somewhat random in my dataset, I would like to avoid using it, which is why I created the calculated Year column using DAX functions. So I would like to use that Year column to create the index. Is it possible to do so without using the dates column for the measure?

[tackytechtom · Accepted solution]

Hi @RH10 ,

How about this:

here the code:

Column = 
RANKX ( 
    FILTER ( 
        Table, 
        Table[Year (Calculated)] = EARLIER ( Table[Year (Calculated)] ) &&
        Table[Course name] = EARLIER ( Table[Course name] )
    ),
    Table[Date],
    , ASC
    , DENSE
)

For reference:

https://www.tackytech.blog/how-to-crack-the-mystery-of-the-mighty-dax/#2_Create_ranks_and_indexes_wi...

Let me know if this helps! 🙂

By the way, I would still recommend to solve it in Power Query. You could even create your calculated column in PowerQuery, too. But since your requirement was to do it in DAX, I provided the solution in DAX as well.

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[RH10]

Hello @tackytechtom,

Thank you for your solution. Unfortunately, it does not work properly and returns an error. My actual data is somewhat messy, as this project that I'm working on is supposed to be updated throughout the year. As of now, there are courses that don't have a specific date and are left blank, or just has a month, or just has the year. I apologize for not making this clear earlier. But since the date column is somewhat random in my dataset, I would like to avoid using it, which is why I created the calculated Year column using DAX functions. So I would like to use that Year column to create the index. Is it possible to do so without using the dates column?

[tackytechtom]

Hi @RH10 ,

Thanks for your reply.

The calculated year and Course name columns are used to do the grouping of your index. The date column is used to sort within the grouping figuring out which index comes first, second, third etc. If the order does not really matter, it should still work to use the Date column as the sorting column, shouldn't it? Alternatively, you could use another column in your dataset to sort upon. Do you have more columns?

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[RH10]

Hello again @tackytechtom ,

Using the date column led to repeated index values because of how messy the date column is, so I set the Table[Date] part to another column as you suggested and now it works. Thank you very much!

[amitchandak]

@RH10 , Try a new measure like

countx(Filter(Table, [course name] = earlier([course name])  && [Date] <= earlier([Date]) ), [course name])

[RH10]

Hello @amitchandak ,

Thank you for your solution. Unfortunately, it does not work properly. My actual data is somewhat messy, as this project that I'm working on is supposed to be updated throughout the year. As of now, there are courses that don't have a specific date and are left blank, or just has a month, or just has the year. I apologize for not making this clear earlier. But since the date column is somewhat random in my dataset, I would like to avoid using it, which is why I created the calculated Year column using DAX functions. So I would like to use that Year column to create the index. Is it possible to do so without using the dates column for the measure?