cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k_mathana
Helper II
Helper II

Random Sample per Category

Hi

Could you please help me to figure out this, I need to select randomly 2 order number from each category. How I can do this in power BI?

CategoryOrder Number
Category A1131292
Category A1131240
Category A1131285
Category A1131278
Category A1131287
Category B1131256
Category B1131262
Category B1131259
Category B1131238
Category C1131260
Category C1131245
Category C1131244
Category C1131281
Category C1131240
Category C1131294
Category C1131273
1 ACCEPTED SOLUTION

I don't really know why those 2 specific orders are shown... I just used the standard function SAMPLE(). Please go through the following link and you can further parametrize the function.

https://docs.microsoft.com/en-us/dax/sample-function-dax

 

If this solution does not meet your requirements, then you will have to consider ranking the orders using RANKX() and then use RANDBETWEEN() to generate a random number between the lowest and highest rank of each category and then pick any two randomly for each category, then do a cross join or use GENERATE in that context.

View solution in original post

3 REPLIES 3
sreenathv
Solution Sage
Solution Sage

Assume that you have the following table named "Orders"

sreenathv_0-1614849032504.png

The following calculated table expression gives you the sample table.

sreenathv_1-1614849078059.png

 

I don't really know why those 2 specific orders are shown... I just used the standard function SAMPLE(). Please go through the following link and you can further parametrize the function.

https://docs.microsoft.com/en-us/dax/sample-function-dax

 

If this solution does not meet your requirements, then you will have to consider ranking the orders using RANKX() and then use RANDBETWEEN() to generate a random number between the lowest and highest rank of each category and then pick any two randomly for each category, then do a cross join or use GENERATE in that context.

View solution in original post

Thank you so much for your time @sreenathv, Yes that becomes deterministic value. I did the same as you suggested added RAND() column and created a table as follows

 

Sampler = GENERATE(
ALLNOBLANKROW( Orders[Category] ),
CALCULATETABLE(
TOPN(
2,
SELECTCOLUMNS(Orders, "Order Number", [Order Number], "RanNum", [RanNum]),
[RanNum],ASC
)
)
)

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

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

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.