How to add a column to indicate max 10 for each group

[Anonymous]

Hei,

I have a table like this in Query Editor. Now I want to add a new column to indicate that this row is the latest 10 in each group of Value1. In other words, I want to find, for each A, B and C in Value1, the lastest 10 rows while latest 10 is defined by DateOfReport. I know how to do it if I only want the latest, but now I want the latest 10........ Thanks in advance!

Value1	DateOfReport	Value2	Value3	Value4	Value5
A	3/11/2020	0.376459	4.532191	0.514858	1.317926
A	3/11/2020	0.939587	6.178669	0.054657	2.015988
A	3/11/2020	6.397195	7.390457	1.628625	1.611496
A	3/11/2020	3.449058	8.557873	0.285234	7.178153
A	3/11/2020	3.379124	4.341741	8.102646	7.96348
A	3/11/2020	2.209457	2.191702	7.478076	2.195619
A	2/18/2020	4.124133	2.104014	8.415438	0.162648
A	2/18/2020	5.226012	6.182481	7.017138	2.128309
B	2/18/2020	2.072618	0.499554	6.893457	0.571343
B	2/18/2020	3.405549	3.292317	5.138828	2.804114
B	2/18/2020	4.562076	6.817683	9.560731	9.90175
B	3/12/2020	7.025505	0.714299	8.700854	9.370493
B	3/12/2020	5.405035	5.600159	9.487718	2.259473
B	3/12/2020	4.616965	1.269989	4.626788	4.435011
B	3/12/2020	9.987548	6.482954	7.517648	3.506048
B	3/12/2020	5.606296	6.969561	5.837276	3.639896
B	3/12/2020	3.560798	2.085465	1.156444	4.981463
B	3/16/2020	3.473538	0.331683	7.616982	2.844292
B	3/16/2020	6.251746	1.037205	8.58943	4.894561
B	3/16/2020	4.992836	4.606678	7.38728	8.850803
B	3/16/2020	6.995705	0.637251	0.79063	3.31176
B	3/16/2020	1.85876	0.218021	1.510057	8.609189
B	3/16/2020	0.765308	7.782757	3.937727	4.046804
B	2/25/2020	3.827979	3.835024	1.035565	1.991558
C	2/25/2020	5.662464	7.782689	6.379318	4.408028
C	2/25/2020	8.063651	8.411202	0.517235	0.598607
C	2/25/2020	9.315234	4.680901	9.626702	7.988079
C	2/25/2020	7.030936	2.349398	4.663467	8.218761
C	2/25/2020	1.352625	4.646801	3.38807	5.574083
C	2/26/2020	3.169367	5.764253	6.842459	0.905843
C	2/26/2020	6.853327	3.714052	1.004112	7.345359
C	2/26/2020	3.227943	6.641982	4.389373	6.046594
C	2/26/2020	8.302275	9.347162	9.472101	7.484054

[v-diye-msft · Accepted solution]

Hi @amitchandak 

Kindly let me know if you'd like to get this one:

Then filter the column no more than 10

Column = var a = 'Table'[Value1]
Return
RANKX(FILTER('Table',[Value1]=a),'Table'[DateOfReport],,ASC)

 

[Anonymous]

Thanks, but i have already accepted a previous answer as solution and cant accept another... 

[amitchandak]

Try if this can help

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

Can you give example of output

[Anonymous]

This works and I have accepted it as solution... 🙂

[Anonymous]

Hi,

Thanks for tips. But I am not quite sure this will work. Ranking is a good idea, but I need to rank by Value1 and this Value1 might have many many values that make it hard to list them manually...

[AilleryO]

Hi,

Can a filter on Value1, using a TOP N filter based on date, be usefull ?

Select your table and column Value1 and use filter panel to create a TOP N filter.

Choose your date and a Max function for the value of your TOP N.

Is that of any help ?

[Anonymous]

Sorry... not really... i need a column in Query Editor...