Lookup on previous rowindex and criteria

[MrMarshall]

I need to lookup the closest value from previous rows based on the criteria (Group < 900). There is an Index column so we know what order to put the rows in.

I took out the following subset of data and I need help to calculate the "Desired Result" column.

For each row, I need to find the first row with (group < 900) from the rows above and I cannot figure this one out.

Index	OrderNr	OrderRow	Group	Desired Result
1	10000	1	110	110
2	10000	2	900	110
3	10000	3	910	110
4	10000	4	900	110
5	10000	5	120	120
6	10000	6	910	120
7	10000	7	905	120
8	10000	8	900	120
9	10000	9	910	120
10	10001	1	100	100
11	10001	2	910	100
12	10001	3	905	100

My first try was to filter on OrderNr and use the lowest Group on each OrderNr, but there can be multiple values below 900 for each OrderNumber so that didn't work.

Next thoght was to use the Lookup function, but that will error out since there is a lot of rows matching, I only want the closest one... 

LOOKUPVALUE(test[Group], test[Group], test[Group] < 900)

Ideas? 

[MFelix]

Hi @MrMarshall,

Try the following measure:

Measure =
CALCULATE (
    MAX ( Test[Group] );
    Test[Group] < 900;
    ALLEXCEPT ( Test; Test[OrderNr] );
    FILTER ( ALL ( Test[Index] ); Test[Index] <= MAX ( Test[Index] ) )
)

Regards,

MFelix

[MrMarshall]

Thanks @MFelix!

As a measure, that works like a charm! 
Now I realized that i badly need this as a column... 

Would this info be possible rewrite this to get as a column instead?

The one you provided does not work as Column I am afraid. 

Th

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=LOOKUPVALUE(Table1[Group],Table1[Index],CALCULATE(MAX(Table1[Index]),FILTER(Table1,Table1[OrderNr]=EARLIER(Table1[OrderNr])&&Table1[Group]<900&&Table1[Index]<=EARLIER(Table1[Index]))))

Hope this helps.

[MFelix]

Hi @MrMarshall,

Why there is the neees to have it as a column?

A good.practice is to create measure when.you can, because they do not.add size to your data and are more flexible.in terms of the use you can.give them because they are context related.

Regards,
MFelix

[MrMarshall]

Hi!

I need to create other columns based on this one, and I also need to use the calculated column as Row value in a matrix.

[MFelix]

Hi @MrMarshall,

To use the measure value in a matrix you just need to change the settings on the matrix and choose the option SHOW ON ROWS.

Regarding the calculation of another column you can always use a measure to calculate another measure however try the following code to create a new column instead of a measure:

Maximum_Per_group = 
var Temp_Table = CALCULATE(MAX(Test[Group]);FILTER(Test;Test[Index]<EARLIER(Test[Index]));Test[Group]<900;FILTER(Test;Test[OrderNr]=EARLIER(Test[OrderNr])))
return
if(Temp_Table= BLANK();Test[Group];Temp_Table)

Regards,

MFelix