Forum Discussion
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?
| Category | Order Number |
| Category A | 1131292 |
| Category A | 1131240 |
| Category A | 1131285 |
| Category A | 1131278 |
| Category A | 1131287 |
| Category B | 1131256 |
| Category B | 1131262 |
| Category B | 1131259 |
| Category B | 1131238 |
| Category C | 1131260 |
| Category C | 1131245 |
| Category C | 1131244 |
| Category C | 1131281 |
| Category C | 1131240 |
| Category C | 1131294 |
| Category C | 1131273 |
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.
3 Replies
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.
Thank you so much for your time Anonymous, 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)))
