cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors