How to create an Index column in Power Query based on 4 columns, and conditional criteria

[aar0n]

Hi Guys, I'm new to Power Query and DAX, and i need to create a column in power query that makes a 'planned order'.

so far, i have sorted my columns based on "ID" and "Location", and then used an index function that resets itself every time a new Location is shown..  but i also need to factor in the "chance of success", as well as the "type"

What i need is:  if the well is "Type" - V, and if the chance of success is less than 50, and the COS is less than the next "Location" --show 0, and for the next Location, show 1, and so on and so forth..

However,  i also have another "Type" column, that can show one of 3 values: a blank value, H, or V. 

if there is a blank value in "Type", there is always a 0 in the planned order,

H values are always 1 in the planned order,

and V values follow the "chance of success" rules outlined above...

an example of what i need for the planned order column is shown below. 

doing this in the query editor would be ideal (to use it in other query manipulations after this is done), but a DAX function can work too.. 

ID	Location	TYPE	Planned Order	Chance of Success (%)
100	c		0	25
100	b	V	1	80
100	a	V	2	75
200	b	V	1	50
200	a	V	2	70
300	d	H	1	80
300	c	V	1	70
300	b	V	2	50
300	a	V	3	50

[Phil_Seamark]

HI @aar0n

This DAX calculated column solves your problem based on your sample data.  But doesn't take into account anything to do with the [Chance of Success] field.  Your description didn't make sense with your sample data/output.  

Can you tweak your data this doesn't do what you need?

Planned Order = 
VAR x  = 
    CALCULATE(
           COUNTROWS('Table1') ,
           FILTER(
               'Table1',
               'Table1'[ID] = EARLIER('Table1'[ID])
               && 'Table1'[TYPE] = "V"
               && 'Table1'[Location] > EARLIER('Table1'[Location])
           )
     )+1
RETURN 
    SWITCH(
        TRUE(),
        'Table1'[TYPE] = "H" , 1 ,
        'Table1'[TYPE] = "" , 0 , 
        x
        )

[aar0n]

Hi, 

here are 2 more scenarios for a sample of the data i have.

ID	Location	TYPE	Chance of Success (%)	Planned Order (without including the COS)	Planned Order (Including COS)
400	g	V	40	1	0
400	f	V	75	2	1
400	e	V	50	3	2
400	d	V	70	4	3
400	c	V	70	5	4
400	b	V	50	6	5
400	a	V	50	7	6

Here is another example:

ID	Location	TYPE	Chance of Success (%)	Planned Order (without including the COS)	Planned Order (Including COS)
500	e	V	50	1	1
500	d	V	66	2	2
500	c	V	45	3	0
500	b	V	80	4	3
500	a	V	20	5	0

This Planned order (including COS)  only  applies to "type" V

the data is all in one table.. with about 4000 rows..  i'm just showing an example of what the final stage needs to look like

[Phil_Seamark]

Hi @aar0n

I guess it just might be helpful to show extra scenarios in your sample data that demonstrate what you are after.

[aar0n]

i've attached a better example..

[v-huizhn-msft]

Hi @aar0n,

I am still confuse about the calculation rules of Planned Order (without including the COS)  and Planned Order (Including COS), you should describe in details. In addition, there is only one issue in one thread, you should mark the right reply as answer if your original thread has been resolved, and please create another new thread if you have new issues.

Thanks,
Angelia

[aar0n]

i apologize for the confusion Angelia,

my original question wasnt fully answered.

basically, @Phil_Seamark 's answer is almost correct. what i'm looking for will have a similar structure to Phil's answer, with the addition of something similar to what is shown in red : 

i want the formula to add 1 to the formula for the "V" types, only if the COS>Earlier COS, or if COS>50 , otherwise i want the formula to show 0. 

Planned Order = 
VAR x  = 
    CALCULATE(
           COUNTROWS('Table1') ,
           FILTER(
               'Table1',
               'Table1'[ID] = EARLIER('Table1'[ID])
               && 'Table1'[TYPE] = "V"
               && 'Table1'[Location] > EARLIER('Table1'[Location])
           )
     )+1 if (COS > EARLIER(COS)) or (COS > 50) 
                                                     Else show 0 for that particular row








RETURN 
    SWITCH(
        TRUE(),
        'Table1'[TYPE] = "H" , 1 ,
        'Table1'[TYPE] = "" , 0 , 
        x
        )

Below, i am using one of my previous examples i posted, and im showing how i would do the formula in excel - for One "ID" that is only type "V".

column I and column F are the same, the only reason theyre being shown twice here is to show that the end result is the same.

column g and h are only being used as "helper columns".

**the formula in G,H, and I shows the formula for the row directly below.

A	B	C	D	E	F	G	H	I
ID	Location	TYPE	Chance of Success (%)	Planned Order (without including the COS)	Planned Order (Including COS)	IF(OR(IF(D2>=50,1,0), IF(D2>D1,1,0)),1,0)	SUM($G$2:G2)	IF(H2=H1,0,H2)
500	e	V	50	1	1	1	1	1
500	d	V	66	2	2	1	2	2
500	c	V	45	3	0	0	2	0
500	b	V	80	4	3	1	3	3
500	a	V	20	5	0	0	3	0