How to ANY(SELECT randomly choose one value from a dynamic List of names

[StephenMattison]

I have this Expression working to automatically fill in the [Organizer] in my Meeting scheduling app. But it always returns the same value. The list of Active Members can get bigger or smaller at any time.

ANY(SELECT(Active Members Slice[_ComputedKey],([Club] = [_THISROW].[Club])))

1. How do I use something like RANDBETWEEN with this expression to give me a more "random" choice?

2. I have several other Roles after Organizer that I also want to Autofill. How would I construct an expression that would only Autofill each Member into one Role?

3. How would I make this Autofill optional by using my [Autofill?] column that has only one choice of "Autofill"?  I am thinking for this to be optional, I would put the ANY(SELECT RANDOM expression in the Initial Value area and then have it only work if [Autofill?]="Autofill". 

Thank you!

[Marc_Dillon]

1. Replace the ANY() with INDEX( .... , RANDBETWEEN() )

2. Not really sure what you mean, probably the same way that you did in [organizer]?

3. Use something like IFS( [autofill] , {existing-expression} )

[Steve]

IFS(
  [Autofil?],
    INDEX(
      FILTER(
        "Active Members Slice",
        ([Club] = [_THISROW].[Club])
      ),
      RANDBETWEEN(
        1,
        COUNT(
          FILTER(
            "Active Members Slice",
            ([Club] = [_THISROW].[Club])
          )
        )
      )
    )
)