Forum Discussion
Selecting Random set of Records from a Dataset
Hello all,
New Power BI user.
Tried some examples found on the web but I cannot seem to apply them to this scenario.
I have a dataset of purchases for various branches around the United States. I am looking to extract a percentage of them for auditing purposes. More precisely, a percentage by state. For example:
The states have the following number of records:
Alabama 1000
Nebraska 250
California 3750
I want to extract 10% of the records. So
100 of Alabama
25 of Nebraska
375 for California
I have tried creating a column using Number.Random & Number.RandomBetween but every record gets the same random number so I can't use it to select say "the top 10%".
Any guidance would be greatly appreciated.
RAND function return a random value for each row. Please refer the screenshot below.
Regards,
Charlie Liao
6 Replies
- Greg_Deckler
Community Champion
Hmm, you could do this in DAX if you perhaps have a query by state and then create a calculated table where you grab a number of records less than some index. So, in Alabama create a calculated table of and index <= 100. Kind of hacky. Will try to think of a more elegant solution.
- Greg_Deckler
Perhaps look at Table.Range
https://msdn.microsoft.com/en-us/library/mt260780.aspx
Also, maybe Table.SelectRows and provide a condition that the row index mod 10 is 0?
Thank you smoupre.
These have promise. But I need to assign a random value to each record first. I think I need to create a new column and assign a random value to each record. Tried using Number.Random but it provides the same value to each row. Once, I have the values assigned your suggestion will be a good next step so I appreciate that. I may have to go the DAX route.
Appreciate your help.
- OwenAuger
Super User
Have a look at this post, in particular Colin Banfield's reply:
