Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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)
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)
Thanks, but i have already accepted a previous answer as solution and cant accept another...
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
This works and I have accepted it as solution... 🙂
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...
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 ?
Sorry... not really... i need a column in Query Editor...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |