Issue with making Custom index for filtered data.

[sc_1991]

Hi, I have a large Dataset. I am trying to create a custom identifier column based on a few filters. 

Here is an example of what type of data I have (This is just a subset of the larger dataset, including the columns. I am just showing the columns that matter the most for my custom column)

Program	Truck	Engine Time	Oil Hour
P1	T1	100	1.2
P1	T1	200	25
P1	T1	300	35
P1	T1	500	40
P1	T1	800	12
P1	T1	900	22
P1	T1	1500	10
P1	T1	1600	50
P1	T2	100	12
P1	T2	200	33
P1	T2	300	45
P1	T2	350	6
P1	T2	400	12
P2	T3	150	5
P2	T3	250	10
P2	T3	350	15
P2	T3	400	35

if you look at the table you can see I have multiple Programs and within each program, I have multiple trucks. 

What I want is a method to group the data for each truck within each program, sort the engine time in ascending order for each individual truck, and then examine the data presented in the Oil Hours column to identify instances where the current oil hour is lower than the previous data point. Subsequently, I would like to display this information in a new column. Please refer to the following example for clarification.

Program	Truck	Engine Time	Oil Hour	Custom Marker
P1	T1	100	1.2	A1
P1	T1	200	25	A1
P1	T1	300	35	A1
P1	T1	500	40	A1
P1	T1	800	12	A2
P1	T1	900	22	A2
P1	T1	1500	10	A3
P1	T1	1600	50	A3
P1	T2	100	12	A1
P1	T2	200	33	A1
P1	T2	300	45	A1
P1	T2	350	6	A2
P1	T2	400	12	A2
P2	T3	150	5	A1
P2	T3	250	10	A1
P2	T3	350	15	A1
P2	T3	400	35	A1

or color coding the regions 

So, to summarize

This formula does the following:

1. It filters the data for each "Truck" within each "Program."

2. Within each group, it sorts the data by "Engine Time" in ascending order.

3. It then checks the "Oil Hour" values and repeats the "A" marker until the "Oil Hour" value decreases.

Thanks,

[amitchandak]

@sc_1991 , You can create Rank in DAX

Rankx(filter(Table, [program] = earlier([Program]) && [Truck] = earlier([Truck]) ), [Engine Time],,asc,dense)

Column Rank: https://www.youtube.com/watch?v=wDS_Vi4r9I4&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=39

Option in Power Query

Power BI and Power Query- Sub Category Rank and Index: https://youtu.be/tMBviW4-s4A

[sc_1991]

HI @amitchandak I could not get this solution to work. I think I am trying to do something that might not ve very possible for Power BI. Let me redifine my problem may be I nade my post very complex. 

I have a table that has Program, Truck, Engine Time and Oil Hour. Now within each program there are multiple trucks. Now what I want is when the user selects a particular truck. Somewhere in the backend I extrat the Engine Time and Oil Hours for that particular truck and then sort the Oil Hours based on the ascending sort of Engine Time. Now, for every instance where I see the present Oil Hours less than previous one I want to mark that as A1 and then keep increamenting everytime I see it for that truck. This Oil Hour data is cyclic in nature so I will see many other instances. So the marker would say A1,A2,A3 and so on. Do you think this is possible in Power BI ?