Forum Discussion
Random Sample
Hi everyone,
Wondering if Power BI can select a random sample however with criteria. I have a data set and want a sample that has a good spread over 4 columns in the data.
For example I want a good spread over Rating, Classification, Class and Business area in the data set below.
e.g. data
| Customer Name | Rating | Classification | Class | Business Area | Value |
| A | 10 | Good | Fail | UK | 156000 |
| B | 15 | Watch | Good | USA | 157 |
| C | 12 | Good | Sub | UK | 850000 |
| D | 14 | Stressed | Good | UK | 984500 |
| E | 12 | Stressed | Good | UK | 178130 |
| F | 12 | Good | Fail | USA | 648491 |
| G | 12 | Watch | Sub | EU | 1658403 |
| H | 10 | Good | Sub | UK | 97 |
| I | 10 | Stressed | Good | UK | 1654846546 |
| J | 10 | Watch | Fail | USA | 548 |
| K | 15 | Stressed | Good | EU | 2126654 |
| L | 19 | Good | Fail | UK | 21687 |
| M | 19 | Stressed | Good | UK | 1576 |
| N | 18 | Good | Sub | USA | 5654698 |
| O | 12 | Stressed | Good | UK | 3216768 |
| P | 15 | Watch | Good | UK | 351648 |
| Q | 19 | Good | Fail | UK | 115849 |
Thanks
Thanks for the response. I think I have solved with some Dax which creates a table for each column I want to randomise and forms a union of those tables. Its a long process but I think it will work. I just create a table per column for the sample spread?
Random Selection with category filter and sample size =
VAR SampleSize = XX
VAR Cat_Name1TableWithRand =
TOPN (SampleSize, ADDCOLUMNS ( FILTER('Table_Name', 'Table_Name'[Column1] = "Cat_Name1"), "Rand", RAND () ), [Rand])
VAR Cat_Name2TableWithRand =
TOPN (SampleSize, ADDCOLUMNS ( FILTER('Table_Name', 'Table_Name'[Column1] = "Cat_Name2"), "Rand", RAND () ), [Rand])
VAR Cat_Name3TableWithRand =
TOPN (SampleSize, ADDCOLUMNS ( FILTER('Table_Name', 'Table_Name'[Column1] = "Cat_Name3"), "Rand", RAND () ), [Rand])
VAR UnionTable =
UNION (Cat_Name1TableWithRand , Cat_Name2TableWithRand , Cat_Name3TableWithRand )
RETURN UnionTable
If there is a quicker way, then great!
3 Replies
Hi, D_HL
Do you want to randomly generate rate from the data in the next four columns?
There is no such method in powerbi, you need to define a complete rule yourself according to the classification.
Best Regards,
Community Support Team _ JaneyThanks for the response. I think I have solved with some Dax which creates a table for each column I want to randomise and forms a union of those tables. Its a long process but I think it will work. I just create a table per column for the sample spread?
Random Selection with category filter and sample size =
VAR SampleSize = XX
VAR Cat_Name1TableWithRand =
TOPN (SampleSize, ADDCOLUMNS ( FILTER('Table_Name', 'Table_Name'[Column1] = "Cat_Name1"), "Rand", RAND () ), [Rand])
VAR Cat_Name2TableWithRand =
TOPN (SampleSize, ADDCOLUMNS ( FILTER('Table_Name', 'Table_Name'[Column1] = "Cat_Name2"), "Rand", RAND () ), [Rand])
VAR Cat_Name3TableWithRand =
TOPN (SampleSize, ADDCOLUMNS ( FILTER('Table_Name', 'Table_Name'[Column1] = "Cat_Name3"), "Rand", RAND () ), [Rand])
VAR UnionTable =
UNION (Cat_Name1TableWithRand , Cat_Name2TableWithRand , Cat_Name3TableWithRand )
RETURN UnionTable
If there is a quicker way, then great!
Hi, D_HL
Glad you found a solution.🤗
Okay, If your problem has been solved, you can mark your answer as solution to close the thread.
Best Regards,
Community Support Team _ Janey
