Forum Discussion
First Minimum, Second Minimum and 3rd minimum value calculation
Dear All,
i am new to DAX and i want to know that how to calculate first min, second min and third min value in a column. i.e.
| Item | Amount |
| 123444556 | 60 |
| 123444556 | 90 |
| 123444556 | 70 |
| 123444556 | 55 |
| 123999666 | 20 |
| 123999666 | 50 |
| 123999666 | 30 |
| 123999666 | 15 |
| 999333547 | 10 |
| 999333547 | 40 |
| 999333547 | 20 |
| 999333547 | 5 |
so these 3 items formula should give me first minimum, second and third minimum value.
Thanks in Advance
Anonymous , I think Rank should help you
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-finaleDepending on your requirement:
RankX1 = RANKX('Table',[Amount],,ASC)orRankX2 = RANKX(FILTER('Table',[Item]=EARLIER('Table'[Item])),[Amount],,ASC)You can then just filter for 1-3 in any visual.
6 Replies
Anonymous across full table or 1st, 2nd, 3rd for each item, it is going to be different solution based on your requirement.
Anonymous , I think Rank should help you
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-finaleDepending on your requirement:
RankX1 = RANKX('Table',[Amount],,ASC)orRankX2 = RANKX(FILTER('Table',[Item]=EARLIER('Table'[Item])),[Amount],,ASC)You can then just filter for 1-3 in any visual.In theory, you could also probably use a TopN filter I suppose...
