Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
orourkebp
Frequent Visitor

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.

1 ACCEPTED SOLUTION

@orourkebp,

 

RAND function return a random value for each row. Please refer the screenshot below.
Capture.PNG

 

Regards,

Charlie Liao

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

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?

https://msdn.microsoft.com/en-us/library/mt260810.aspx


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

@orourkebp,

 

RAND function return a random value for each row. Please refer the screenshot below.
Capture.PNG

 

Regards,

Charlie Liao

Thank you, Charlie.

 

I was actually looking for a solution to part of the query step which is probably not the correct way of thinking.  

 

I appreciate your feedback.

 

Brian

Have a look at this post, in particular Colin Banfield's reply:

https://social.technet.microsoft.com/Forums/en-US/81ca82f3-fa9f-4e49-935b-33b42b257e41/random-number...


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.