finding the lowest three values in a column

[monojchakrab]

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})

[PaulDBrown · Accepted solution]

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?

[monojchakrab]

Excellent @PaulDBrown - crystal clear