DAX random sample

[Christann]

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.

[v-xjiin-msft]

Hi @Christann,

Maybe you can try SAMPLE() function.

Something like:

Thanks,
Xi Jin.

[OwenAuger · Accepted 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

[KenTate11]

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?

[GwynethM]

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

[abhishekpati86]

I think you are looking for RAND() function. Try to read more about it and it should solve your issue.

[Christann]

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!

[abhishekpati86]

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.

https://msdn.microsoft.com/en-us/library/mt260780.aspx

[Christann]

That uses the M langage. Can you combine M and Dax?