Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
The goal is to automate my list managment process for software deployments.
I am looking to create a calculated column, that will take a few filters and row count inter consideration.
For example.. I would first sort by user computer type > user location > user department. Then I would say I want to break this up into waves of 50 people each. So the output would be "Wave 1" for the first 50 users based on the sorting criteria, then as soon as it hit the 51st user, the next 50 users would be "Wave 2"
Idealy I would want to be able to do this in Power BI so I can keep the direct connection to my data sources.
The code for this column should also be able to update relatively easy. For example, if I wanted to add sorts, or filter some things out, change the user count per wave from 50 to 100.. etc..
Any ideas would be super helpful!
Thank you,
Ryan Mulhollem
Solved! Go to Solution.
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
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 |
@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, @Anonymous
EARLIER Function should be used as below:
Earlier(NonMHFTreeUsers[Location])=NonMHFTreeUsers[Location]
And I couldn't find out the logic of your formula, you could share some simple sample data and expected output.
Best Regards,
Lin
Thank you for your response Lin. Let me work on this and let you know if it works!
Also, just a thought. It would be useful if I could grab the category of another column for the naming convention of these migration waves. For example, if the user is located in Asia Pacific, the wave name would be "Asia Pacific - Wave 1"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
101 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |