Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Is it possible to create a new table in DAX which is a random sample of rows from another table?
I would have to be able to refresh the sample table.
Solved! Go to Solution.
Hi @Christann
To create a random sample table with DAX, I would use RAND() as suggested above, and create a table with an expression like this:
Random Selection = VAR SampleSize = 10 VAR TableWithRand = ADDCOLUMNS ( OriginalTable, "Rand", RAND () ) RETURN TOPN ( SampleSize, TableWithRand, [Rand] )
The Random Selection table will end up with an extra column Rand but you can remove that if needed.
It appears that the SAMPLE function returns a deterministic sample.
Regards,
Owen
Hi @Christann
To create a random sample table with DAX, I would use RAND() as suggested above, and create a table with an expression like this:
Random Selection = VAR SampleSize = 10 VAR TableWithRand = ADDCOLUMNS ( OriginalTable, "Rand", RAND () ) RETURN TOPN ( SampleSize, TableWithRand, [Rand] )
The Random Selection table will end up with an extra column Rand but you can remove that if needed.
It appears that the SAMPLE function returns a deterministic sample.
Regards,
Owen
I would like to generate a random sample in this same way, but adding a location, date and time criteria to te sample return? For example, 1 row for every hour on each day at each location. Is this possible?
Hi @OwenAuger
This is a fantastic example! Thank you I have found this very helpful.
To delve deeper into this, is it possible to pick a random sample based on certain criteria? For example I have a column with regions and I need to select a random sample of 3% from each region.
At the moment I can make a table which can calculate the number of lines needed per region based on 3% but is it possible to set a table up as in your example to pick a random sample based on the % and region?
Regards,
Gwyneth
I think you are looking for RAND() function. Try to read more about it and it should solve your issue.
From what I have read, it looks like the RAND() function creates new random numbers. I am looking for a random sampling of rows in a table, to be created as a new table. Thanks though!
I have never tried this , but from an initial look Table. Range might be useful in this case. Though it is not entrely random but you can change the offset criteria.
That uses the M langage. Can you combine M and Dax?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |