Forum Discussion
Resolver IIIfinding the lowest three values in a column
Hi PBI community,
While TOPN solves the problem for finding the top - 3,5, or 10, I could not find a function to do the opposite.
I came across this piece of code but not able to undertand the syntax in the last part, where "IN" is used with a series of numbers within curly brackets.
Can someone help explain this last part of the code for me?
Really appreciate
Rank all rows as Column (CUSTID) =
RANKX(
FILTER(
'Table',
'Table'[CUSTID]=EARLIER('Table'[CUSTID])
),
'Table'[Your Value],,ASC,Dense
)
Then just return values ranked 1,2 and 3 by adding a new measure :
Lowest Values = CALCULATE([Value], 'Table'[Rank all rows as Column (CUSTID)] in {1,2,3})
The curly brackets are an expression to create a table. So {1, 2, 3} creates virtual table (since it´s in a measure) which looks like
The IN operator returns TRUE if a row value exists within a table.
So in the measure you posted, RANKX calculates the rank of the value in ascending order (so the lowest's rank will be 1, the second lowest 2 etc...)
So basically the [Lowest values] measure is calculating the values where the [RANKX] measure returns a value of 1, 2 or 3. (So the three lowest values)
Make sense?
2 Replies
PaulDBrownCommunity Champion
The curly brackets are an expression to create a table. So {1, 2, 3} creates virtual table (since it´s in a measure) which looks like
The IN operator returns TRUE if a row value exists within a table.
So in the measure you posted, RANKX calculates the rank of the value in ascending order (so the lowest's rank will be 1, the second lowest 2 etc...)
So basically the [Lowest values] measure is calculating the values where the [RANKX] measure returns a value of 1, 2 or 3. (So the three lowest values)
Make sense?
monojchakrabExcellent PaulDBrown - crystal clear