Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 .
Cumulative Total
  • daXtreme's avatar
    daXtreme
    4 years ago
    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's avatar
Anonymous
Not applicable
4 years ago

Hello, much as i want to use power query for this, it is a calculated table created in dax hence i will need a dax calculated column. so basically i just need to create a index that will change for every 200 row items. thanks

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Yes you are right i just need to divide the entire table into batches. currently i have 856 rows, so will need to put them in 5 batches. Unfortunately i do not have columns that have unique values

      • daXtreme's avatar
        daXtreme
        Solution Sage
        4 years ago
        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...