Solved: Randbetween formula with no duplicates - Page 2

juanpa · 12-03-2020 11:53 AM

Hi,

I want to create an expression that assigns a random position based on the total number of subscribers in a competition with no duplicates.

The formula I’m using (virtual column) is the following:

RANDBETWEEN(1,COUNT(SELECT(Punctuation[Trial ID],[Trial ID]=[_THISROW].[Trial ID])))

It assigns the position but almost always if I have let’s say 5 subscribers in a competition, some of them have the same position.

Is there any way to assign a different position to each subscriber?

Thanks!

Suvrutt_Gurjar

If I may join the discussion thread. Since the requirement is very interesting, tried to work on an approach. Please explore if the below meets the requirement. Even if not in your case, I hope it may be useful in some other requirement.

Create 3 VCs

[HelpSetOrder]
IFS([_thisrow].[Key]=[Key], RANDBETWEEN(MIN(Table Name[_rownumber]), MAX(Table Name[_rownumber])))
This column creates a different random number for each row. However, this column can have duplicates produced by the RANDBETWEEN() function
[OrderBy Random]
ORDERBY(Table Name[Key Column], [HelpSetOrder], TRUE)
This column sorts the list of keys by [HelpSetOrder]. The main crucial point is since this is ORDRBY() column, it sorts the keys without duplication, even if the sort order input column has duplicates.
[Position]
INDEX([OrderBy Random][_ROWNUMBER], [_rownumber]-1)
This column attaches [_RowNumbers] to the randomly ordered key order in step 2 above and assigns one randomly sorted rownumber value to each row through INDEX().

Currents Status: The sort order changes with each sync as VCs are involved. Will need some expression enabling logic through say action etc. to prevent the expressions kicking in during each sync.

In the picture below the column [HelpSetOrder] has duplicates, highlighted in red. However, the final [Position] column is randomly ordered with unique values or no duplicates as per requirement for each row, as highlighted in green.

View solution in original post