Display Random Subset of Data

[Jingspat]

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

[danextian]

hi @Jingspat

Which one is your goal?

-select just 10 random rows in power query and then load it to the model

-randomly choose 10 rows of the dataset already loaded to the model

[Jingspat]

@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!

[danextian · Accepted solution]

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.

[Jingspat]

I can't seem to change the Visual level filter to anything other than "is less than" - it won't let me click the drop down box, but I can enter a value.  What am I doing wrong?

[danextian]

Hi @Jingspat,

There is no Top N option for measures.  Please select Index and then choose Top N under filter types then drag random measure to By Value.

[Jingspat]

Sorry for not getting it! I still can't change the filter type of Index:

Here is the modeling tab for the Index column:

[danextian]

are you not seeing this option?

[Jingspat]

I wasn't.  I just updated to the latest version and now it is working!  Thank you so much for the help!!!!

Appreciate it!

[danextian]

It didn't come to my mind that you might be using an older version of PBI Desktop but I'm glad to have helped.