Random values generation defined by page slicers

[Anonymous]

Hello,

I am trying to create a random value generator and I have a very simple table representing my data. Basically, I need to display a sample of 3 random users who have not been assessed ('Assessed'=0). The tricky part is that I also need to display the sample based on page slicers. The report user should be able to select a 'Group' and the random sample needs to be affected by the slicer.

I have tried a few things:

- I created a table with only users who have not been assessed:

 and then I used RANDBETWEEN() to get a random name out of that table:

This is itterated 3 times to get the 3 names (they may be the same from time to time but my actual dataset is huge so this should be a 1 in a million scenario). This works fine until I try to filter on 'Group'. I can't seem to get random name out of a subsequently picked via a slicer group. I always get a name out all unassessed users. I need to find a way to apply page slicers on the random selection.

- I also tried SAMPLE(3,'Rand test',0) but this creates a DAX table that also cannot be filtered by page filters and only recalculates on report refresh.

I would appreciate any help with my issue! Thanks in advance!

Regards,

Bogdan Stefanov

[Anonymous · Accepted solution]

Just want to say that I figures out a way to do it. It might not be perfect but it does exactly what I need it to do. I created a parameter and I am filtering my table on that parameter. Every time the parameter changes, the SAMPLE() function is recalculated and I get a random sample from the Group I have selected.

[lbendlin]

You can't do much in DAX with a table result. At the end of the measure you need to have a scalar value.

[Anonymous]

Yes, in case I use a measure, I won't be able to have a table as output, but SAMPLE() would give me a table. I need a table because in some scenarios I would have to make a random list with up to 70 people. I don't want to make 70 measures and not being able to extract them in a single list.

[Anonymous]

Hi @lbendlin,

So yes, I will try to make the requirement as clear as possible.

1. we have a list of people (every person belongs to a group)

2. select a group

3. select what should be my sample size

4. have a random list of people part of the selected group.

What I currently get is a random list of people but the group part is missing. I cannot get the filter to affect the end result. What I also discovered is that I would most likely need a table output, which probably makes SAMPLE() a better option. Can I create a parameter to achive step 3?

Regards,

Bogdan Stefanov

[lbendlin]

Remarks

• Recalculation depends on various factors, including whether the model is set to Manual or Automatic recalculation mode, and whether data has been refreshed.

• RAND and other volatile functions that do not have fixed values are not always recalculated. For example, execution of a query or filtering will usually not cause such functions to be re-evaluated. However, the results for these functions will be recalculated when the entire column is recalculated. These situations include refresh from an external data source or manual editing of data that causes re-evaluation of formulas that contain these functions.

• RAND is always recalculated if the function is used in the definition of a measure.

So supposedly you should be able to do this in a measure based on a filter change event.   Can you strip down the problem statement a bit more? Like, "Have a list of people, apply an arbitrary filter to the list, and then pick two items from the remaining list" - would that be accurate?