Forum Discussion
Solved
Generating Dynamic Random Sample in Power BI with Custom Constraints
Hello Power BI Community, I hope you’re all doing well! I’m currently working on a project where I need to generate a dynamic random sample from a dataset of approximately 4,000 to 5,000 rows, with...
Here is a sample setup, assuming even distribution.
Table:
Samples = ADDCOLUMNS(GENERATESERIES(1,5000),"Rand",RAND())Filter Measure:
Include = if(sum(Samples[Rand])<=[Sample size Value],1,0)If you need it more precise, use percentilex.:
Include = if(sum(Samples[Rand])<=PERCENTILEX.INC(all(Samples[Rand]),[Rand],[Sample size Value]),1,0)Row count measure:
Included Rows = COUNTROWS(FILTER(Samples,[Include]=1))
In Power Query:
- add a random value column
- sort by the new column
- add an index column
(NOTE: Sorting in Power Query is very expensive - I am only proposing it because of your low number of rows)
That's all you need. Now in Power BI you can use a simple filter or PERCENTILEX on the index column.