cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Rand() recalculated within TOPN

The data table used is below

I have a slicer  nValue, with values from 1 to 20.

Case 1 (working as expected):
Based on the slicer selection, the data table should show the top  products based on Index values.

Measures used:

1.  TotalSales = SUM('Table'[Amount])

2.  top_Products =  //sorts the products based on the top N Index values
CALCULATE (
'Table'[TotalSales],
KEEPFILTERS ( TOPN ( nValue[nValue Value], ALLSELECTED ( 'Table' ), 'Table'[Index], DESC ) )
)
)

Fig: table sorted correctly based on top 2  Index values

Case 2 (Not working):
Based on the slicer selection, the data table should sort the Products based on the top N random values.
However, the results don't come as expected.

1. Random = RAND()
2. Top_Random//should sort the products based on the top N rand() values
CALCULATE (
' Table'[TotalSales],
KEEPFILTERS ( TOPN ( nValue[nValue Value], ALLSELECTED ( 'Table' ), RAND(), DESC ) )
)
)

Here, table gets sorted wrongly - Instead of showing value for top 3 Random values, it is showing 2 other rows,  ie; sorting doesn't consider the rand() values.

Will the value of rand() get changed for  each iteration of TOPN?
Is something happening to the rand() inside the TOPN function, that makes it impossible to sort the values?

1 ACCEPTED SOLUTION
Community Support

Hi, @Anonymous

Measure Random and Measure Top_Random perform different random number calculations. The random number generated by Measure op_Random is not the same as the random number generated by Measure Random.

And due to the characteristics of the TOPN function, the returned result may be more than N rows.

• If there is a tie, in order_by values, at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row the function might return more than n rows.

Refer:

Filtering the Top 3 products for each category in Power BI

Filtering the top products alongside the other products in Power BI

Showing the top 5 products and Other row

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi, @Anonymous

Measure Random and Measure Top_Random perform different random number calculations. The random number generated by Measure op_Random is not the same as the random number generated by Measure Random.

And due to the characteristics of the TOPN function, the returned result may be more than N rows.

• If there is a tie, in order_by values, at the N-th row of the table, then all tied rows are returned. Then, when there are ties at the N-th row the function might return more than n rows.

Refer:

Filtering the Top 3 products for each category in Power BI

Filtering the top products alongside the other products in Power BI

Showing the top 5 products and Other row

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.