Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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-
Solved! Go to 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.
Regards,
Daniel He
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.
Regards,
Daniel He
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.
Oh, I changed the field to decimal with 10 digits in the modeling tab and it worked out well. It is fixed! Thanks!
works exactly how i wanted. thanks!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
70 | |
64 | |
51 | |
39 | |
26 |
User | Count |
---|---|
86 | |
55 | |
44 | |
42 | |
36 |