How can I filter an array based on the nth value?

[UsePowerBI]

Hello

From the date-sorted array:

ID1, K, 01/01/20

ID1, R, 02/02/20

ID1, T, 02/03/20

ID1, R, 03/04/20

ID1, S, 03/05/20

ID1, T, 03/06/20

...

I want to return an array that is as the above but only the part below the last "R".

I.e.:

ID1, S, 03/05/20

ID1, T, 03/06/20

Is that possible?

Thanks!

[v-xiaoyan-msft · Accepted solution]

Hi@UsePowerBI,

I make a simple sample，you can create the corresponding time column according to the Test column then find whether there are items in the Test column that meet the criteria of the R column.

try measure like ：

Measure = VAR Q1= CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[r]<>BLANK())) VAR Q2= CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),MAX('Table'[date])>Q1)) RETURN Q2

then you will get ：

Here is the demo：

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/EeG3uzbCIkhJlPopjy8... 

Hope it helps.

Best Regards,
Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[timg]

 Hi,

Could you clarify what the rule is for including or excluding rows in your array? like should the top 4 rows always be excluded? Or should only the last 2 rows always be included? or is there a date after which records should be included? etc.

Regards,

Tim