Forum Discussion
Creating Batches
Hello, need to create a calculated column to label each row item in my table as what batch. So batch 1 is the first 200 row items then batch 2 for the succeeding row items and so on .
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...
9 Replies
Please use Power Query for this, not DAX. But to give you the exact solution... we need data to work with. To be able to do it in DAX, your table would have to have a field to sort it on. This is definitely a job for PQ.
Anonymous
OK... I understand you're not worried about which rows will get which batch number? What kind of columns do you have in the table? Is there one that is unique?