Forum Discussion
Automated List Management
HI, Anonymous
You could try this way:
First add an index column. Refer this post: https://community.powerbi.com/t5/Desktop/Creating-calculated-index-column-in-DAX/m-p/635397#M303922
Second, divide this index column by 50 (Integer)
INT((Table1[Index]-1)/50)
Then get result column
group = "Wave "&INT((Table1[Index]-1)/50)+1
For example:
In my simple sample, I use 3 instead of 50
Best Regards,
Lin
HI, Anonymous
You could try this way:
First add an index column. Refer this post: https://community.powerbi.com/t5/Desktop/Creating-calculated-index-column-in-DAX/m-p/635397#M303922
Second, divide this index column by 50 (Integer)
INT((Table1[Index]-1)/50)
Then get result column
group = "Wave "&INT((Table1[Index]-1)/50)+1
For example:
In my simple sample, I use 3 instead of 50
Best Regards,
Lin
Thank you for your response Lin. Let me work on this and let you know if it works!
v-lili6-msft So technically this does serve as the answer to my question. My only concern is the lack of customization.
Is there a way to make this conditional as well? For example - if I am doing wave 1 up to 50 people, however if the city changes (pre-sorted when making the index), the count should reset to 1. So if there are only 38 people in NYC for example, the count should stop at the 38th person and reset at 39. I know how to do this with Excel just not Power Query / BI
hi, Anonymous
For your requirement, you need to use EARLIER Function to add the index column by group.
Best Regards,
Lin
Hi Lin,
First thank you for your response and help.
The EARLIER Function only works on numeric values. I want the conditional column to be based off of user location such as NYC, Baltimore, China, etc..
This is an example of my failed attempt..
Hi v-lili6-msft
For the example, I changed the count change from 50 to 5 for simplistic reasons. As you can see from below, the count is updating every 5, however if the location changes, the count resets. I am getting thrown an error with the EARLIER function.. my formula is below. Thank you again for your assistance here.
Test Column = COUNTROWS(FILTER(UserList, EARLIER(UserList[Location])= UserList[Location], INT((UserList[Index]-1)/5)+1))
UserName Location Expected Output User 1 NYC 1 User 2 NYC 1 User 3 NYC 1 User 4 NYC 1 User 5 NYC 1 User 6 NYC 2 User 7 NYC 2 User 8 NYC 2 User 9 London 3 User 10 London 3 User 11 London 3 User 12 London 3 User 13 London 3 User 14 Baltimore 4 User 15 Baltimore 4 User 16 Baltimore 4 User 17 Baltimore 4 User 18 Baltimore 4 User 19 Baltimore 5 User 20 Baltimore 5 User 21 Baltimore 5 User 22 Baltimore 5 User 23 Houston 6 User 24 Houston 6 User 25 Houston 6 User 26 Princeton 7 User 27 Princeton 7 User 28 Princeton 7 User 29 Princeton 7 User 30 Princeton 7 User 31 Princeton 8 User 32 Princeton 8