Forum Discussion
Display Random Subset of Data
I have a table with thousands of rows. Instead of displaying all the items, I would like to display 10 random rows of data every time to report loads. Is there an easy way to accomplish this?
Regards,
JingSpat
hi Jingspat
You may use RAND() in DAX either as a calculated column but I would prefer a measure as RAND in a measure is not always recalculated (reference).
I tested how RAND works in a measure. Here's what I did:
- Add an index column to your table in power query. Apply query changes to load the updated table. This is so we can have one unique value for each row in your table.
- Create a measure
ramdom measure = RAND()
- Add desired fields to a table including the index column.
- Go to Visualizations pane > Fields > Visual level filters
- Selected Top N under filter type. Show Top 10 items. Drag random measure to By Value.
9 Replies
danextian Sorry for not being clear. I am relatively new to all this and am not sure of the proper terminology yet. I am using a table visualization that returns thousands of rows and then adds a scroll bar on the report, but I just want it to display 10 randomly selected rows of data and no scroll bar. I believe this correlates to your second scenario "randomly choose 10 rows of the dataset already loaded to the model."
Thanks so much!
hi Jingspat
You may use RAND() in DAX either as a calculated column but I would prefer a measure as RAND in a measure is not always recalculated (reference).
I tested how RAND works in a measure. Here's what I did:
- Add an index column to your table in power query. Apply query changes to load the updated table. This is so we can have one unique value for each row in your table.
- Create a measure
ramdom measure = RAND()
- Add desired fields to a table including the index column.
- Go to Visualizations pane > Fields > Visual level filters
- Selected Top N under filter type. Show Top 10 items. Drag random measure to By Value.
