Forum Discussion
Best Practice for Counting Employees
Couple things come to mind:
1) Your SWITCH( TRUE() ) function works well, but it's a little cumbersome.
Try this:
[Employee Size Range] = VAR EmpNum = [Employee Count] RETURN SWITCH ( TRUE (), EmpNum <= 25, "0-25", EmpNum <= 50, "26-50", EmpNum <= 100, "51-100", EmpNum <= 200, "101-200", "200+" )By creating a variable EmpNum, you don't have to evaluate the [Employee Count] every time. This will run faster.
Also, SWTICH() tries each logical comparison in order, so you don't need to use AND. If it fails the first logical comparison (saying it's less than 25), then by default it will be greater than 25. No need to check that part again.
Power BI always sorts columns in alphabetical order. If you ever want to sort columns in a different order, you need to have a column in your data model to sort by.
Good news is, you can use the same template from your measure to create your sorting column:
EmployeeSizeRangeSort = VAR EmpNum = [Employee Count] RETURN SWITCH ( TRUE (), EmpNum <= 25, 1, EmpNum <= 50, 2, EmpNum <= 100, 3, EmpNum <= 200, 4, 5 )Now, click on the [Employee Size Range] column. Click the MODELING TAB. Then Click SORT BY COLUMN. Click [EmployeeSizeRangeSort]
Hope this helps,
~ Chris H
Couple things come to mind:
1) Your SWITCH( TRUE() ) function works well, but it's a little cumbersome.
Try this:
[Employee Size Range] =
VAR EmpNum = [Employee Count]
RETURN
SWITCH (
TRUE (),
EmpNum <= 25, "0-25",
EmpNum <= 50, "26-50",
EmpNum <= 100, "51-100",
EmpNum <= 200, "101-200",
"200+"
)By creating a variable EmpNum, you don't have to evaluate the [Employee Count] every time. This will run faster.
Also, SWTICH() tries each logical comparison in order, so you don't need to use AND. If it fails the first logical comparison (saying it's less than 25), then by default it will be greater than 25. No need to check that part again.
Power BI always sorts columns in alphabetical order. If you ever want to sort columns in a different order, you need to have a column in your data model to sort by.
Good news is, you can use the same template from your measure to create your sorting column:
EmployeeSizeRangeSort =
VAR EmpNum = [Employee Count]
RETURN
SWITCH (
TRUE (),
EmpNum <= 25, 1,
EmpNum <= 50, 2,
EmpNum <= 100, 3,
EmpNum <= 200, 4,
5
)Now, click on the [Employee Size Range] column. Click the MODELING TAB. Then Click SORT BY COLUMN. Click [EmployeeSizeRangeSort]
Hope this helps,
~ Chris H
Thanks Chris! This worked flawlessly. I have a few other questions about this scenario. So I have my numbers the way I want them on my graph now, with the legend in order:
And here is my formula for 'Employee Count' which I previously did not show that is the VAR for Employee Size Range:
My issue is now when I try to import employees of the status type of 'T', all the numbers on my graph change:
Even if I set a filter on the graph itself to only show 'A' status counts, the numbers do not revert to the numbers in the 1st graph above. I feel it has to do with my 'Employee Counts' formula. I've tried swapping out the 'servicelevel' field I'm using in my ALLEXCEPT clause but nothing seems to work. I'm not sure how to do this piece because with each company they can have multiple services, service levels, company sets and empstatuses. My intent is that regardless of the number of these things they have, I am still able to do a distinct count of the number of companies and their active employee counts (I'm pulling in termed employees for another purpose):Any ideas?
Thanks!
Also to futher clarify, Here's what I mean by different services, service levels, company sets and empstatuses:
Sometimes with having these and trying to achieve distinct counts, there is some form of miscounting or double/triple counting something because of the element of various services etc. a company can have,