Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Creating Batches

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Creating Batches

07-20-2022
04:15 AM

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 .

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-20-2022
07:05 AM

```
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 9

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-20-2022
05:13 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-20-2022
05:37 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-20-2022
05:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-20-2022
07:05 AM

```
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...

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-20-2022
07:31 AM

This is nice! just some few clarification:

1 the first column basically creates a unique value for each row right?

2. what if i already have a unique column but is it is not a number, how will i be able to cummulatively count the rows?

3. If i already have a define length, let's say 200 i can already proceed with

ar BatchId = QUOTIENT( Statistics[Index], Length )

to assign value of length to 200 right?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
01:27 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-21-2022
11:46 PM

Ok one last thing if ever i want to put this in a measure what will the changes to be made?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-25-2022
04:34 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

07-20-2022
05:10 AM

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.

Top Solution Authors

User | Count |
---|---|

132 | |

61 | |

35 | |

34 | |

27 |

Top Kudoed Authors

User | Count |
---|---|

163 | |

56 | |

39 | |

36 | |

27 |