Forum Discussion
Creating Batches
define column // First, create manually this column in your table... 'Statistics'[Helper] = rand() column 'Statistics'[Index] = // Then this one... var currentHelper = 'Statistics'[Helper] var Index = COUNTROWS( filter( 'Statistics', 'Statistics'[Helper] <= currentHelper ) ) return Index column Statistics[BatchId] = // Then this is going to be the one you need. // Hide the other two. Adjust the Batch BatchCount // variable. var RowCount = COUNTROWS( Statistics ) var BatchCount = 5 var Length = int( RowCount / BatchCount ) var BatchId = QUOTIENT( Statistics[Index], Length ) return BatchId // TESTING... EVALUATE 'Statistics' order by Statistics[BatchId]The above is test code that works in DAX Studio with any table you want (mine was called Statistics). All you have to do is to create the 3 columns (in the order of appearance) in your table. The sad thing is that if you want to do such things in DAX in the most general way, you have to use the random numbers...
Anonymous
1. Yes. But it's possible with rand(), even though highly improbable, that you could get 2 same values. But that should not matter much...
2. If you have a unique column and its values are comparable (even strings are), then you can use it instead of the one with rand(). The logic is exactly the same.
3. Yes, instead of defining how many batches you want, you can directly specify how long a batch
should be. Then, you don't have to calculate the length of the batch and the formula above is correct.
Ok one last thing if ever i want to put this in a measure what will the changes to be made?
Anonymous
Don't ever put this in a measure because it relies on RANDOM NUMBERS. This means every time you run it, rows can be assigned a different batch id... and unless you're OK with that (are you?), you should not use it as a measure.