How to generate random number based on seed value?

[ppgandhi11]

Hi,

I am trying to generate random number in power BI desktop but I need to do it based on the seed value. MemberID in below example is a seed value. MemberID is not unique. So for a given memberID, the generated random number must be same.

MemberID  RandomNumberMeasure

1                    0.5

2                    0.4

3                    0.6

2                    0.4

1                    0.5

5                    0.75

6                    0.80

As you can see, the generated random number for memberID 1 is always 0.5 and for memberID 2 is always 0.4

How can I achieve this? Rand function does not take any seed value so rand(MemberID) becomes invalid syntax.

Any help is appreciated.

Thanks. Prashant-

[v-danhe-msft · Accepted solution]

Hi @ppgandhi11,

Based on my test, you can refer to below steps:

1.I have entered some sample data to test for your problem like the picture below:

2.

2.Create a new table

RandTable = SUMMARIZE('Table1',Table1[ID],"A",RAND())

3.Create relationship between the two tables.

4.

4.Create a calculated column in ‘Table1’ and now you can see the correct result.

Rand = RELATED(RandTable[A])

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/ejj1iai8rt580cv/How%20to%20generate%20random%20number%20based%20on%20seed%...

Regards,

Daniel He

[ppgandhi11]

upon looking deeper, actually it does not seem to be working. The random numbers are always generated with only 2 decimal places (i think that is the problem). The range it picks is: 0.00 to 1.00 I am using rand() function.

It generates same random number for same ID which is correct, but it is generating same random number for different IDs too which is incorrect.

Below is the sample result returned.

ID   RandomNumber

1     0.01

2     0.05

3     0.05

4     0.95

1     0.01

As you can see above, for ID 1, it generates 0.01 consistently so that is fine.

However, for both ID 2 and 3, it generates 0.05 which is incorrect.  It should have generated 2 different random numbers for them.

How to achieve this? Ideally the random number generated should be large decimal (e.g. 7-8 decimal places) to avoid this kind of situations. Thanks!

Your help is much appreciated.

[ppgandhi11]

Oh, I changed the field to decimal with 10 digits in the modeling tab and it worked out well. It is fixed! Thanks!

[ppgandhi11]

works exactly how i wanted. thanks!