Rand() changing Table Content every time .pbix is openned

[ebravo]

Hey, 

I am using Rand() function to create a table. The problem is everytime I close the .pbix and then open it again, even if I don't refresh any data, just by openning it, the table is recalculated according to that Rand. How can I avoid that? I am using it to create Control Groups and need them to remain the same across time.

Below there is an example of the code in case it helps, very simple.

Thanks a lot in advance for the help!

Code:

Tabla Base Audiencia Extended Care_CONTROL_GROUP Inter =

VAR ExtendedCare_Audiences =

COUNTROWS ( 'Tabla Base Audiencia Extended Care Inter' )

VAR SampleSize = ExtendedCare_Audiences * 0.1

VAR TableWithRand =

ADDCOLUMNS(

'Tabla Base Audiencia Extended Care Inter',

"Rand", RAND())

RETURN

TOPN(SampleSize, TableWithRand, [Rand])

[ebravo · Accepted solution]

Update: so as I have been investigating a bit, rand formula will have this behaviour. As a workaround, I created the rand numbers in the previous table as a calculated column, with RANKX formula. Of course this will not be valid for everyone as my "User Id" is an alpha-numeric code, but if it were a number you would probably get them sorted.

[lbendlin]

You cannot avoid that in Power BI.  Create your random numbers in your data source instead.

[ebravo]

Thanks for the reply. Although this was always an option of course, I wanted (if possible) to do it within dax as filters to main table (enriched with different ones from different sources apply, the example I sent is the simplest one but others are more tricky). But of course if someone can do it outside PBI, that's always an option.