Forum Discussion
Power Pivot, help with binning / grouping
Hi there!
I need some help for work, as i seem to be having trouble binning / grouping values to make it visually better in an excel driven dashboard using power query and power pivot. (yes thats right, not Power BI Desktop due to publication limitations)
I currently have a fact table with data, containing a column with a datediff to calculate how many worksdays an order has taken to process. Now i want to group them with labels i have in a dimension table containing a high and low, so that i can later add or remove labels easily.
Now i have tried using the if function, which becomes very large and not very easily editable. I have tried the switch function, but don't seem to be able to make that one work, so i turn to you!
Fact table layout: (fictious data)
| Unique ID | begindate | enddate | workdays |
| tk0001 | 1-1-2020 | 2-1-2020 | 2 |
| tk0002 | 2-1-2020 | 4-1-2020 | 2 |
| tk0003 | 3-1-2020 | 6-1-2020 | 2 |
| tk0004 | 4-1-2020 | 8-1-2020 | 3 |
| tk0005 | 5-1-2020 | 10-1-2020 | 5 |
| tk0006 | 6-1-2020 | 12-1-2020 | 5 |
| tk0007 | 7-1-2020 | 14-1-2020 | 6 |
| tk0008 | 8-1-2020 | 16-1-2020 | 7 |
| tk0009 | 9-1-2020 | 18-1-2020 | 7 |
| tk0010 | 10-1-2020 | 20-1-2020 | 7 |
| tk0011 | 11-1-2020 | 22-1-2020 | 8 |
Dimension table layout:
| label | low | high |
| <1 workday | 0 | 1 |
| 1 workday | 1 | 2 |
| 2 workdays | 2 | 3 |
| 3 workdays | 3 | 4 |
| 4 workdays | 4 | 5 |
| 1 week | 5 | 10 |
| 2 weeks | 10 | 15 |
| 3 weeks | 15 | 20 |
| 4 weeks | 20 | 25 |
How would i combine the 2, to create a column in the fact table with the values mentioned in the dim table?
The solution from Ricardo is a clever way to do it in M. If you want to do it in DAX, here is an expression you can use in a calculated column to get your desired result.
label = var currentworkdays = 'Fact'[workdays]return CALCULATE(MIN('Dimension'[label]), 'Dimension'[low]<=currentworkdays, 'Dimension'[high]>=currentworkdays)If this works for you, please mark it as the solution. Kudos are great too. Please let me know if it doesn't or if any questions.
Regards,
Pat
3 Replies
Hi decarsul ,
You can try this code for a new column:
let _workDays = [workdays] in
Table.SelectColumns(Table.SelectRows
(tb_group, each
_workDays >= [low] and
_workDays < [high]), "label")Did I answer your question? Mark my post as a solution!
Ricardocamargos88 i like your way of thinking, however i didn't have the networkdays calculated thru powerM query's unfortunately. Briefly looked into it and couldn't figure it out. So even tho this would be the smarter choice, i've taken the DAX route suggested by mahoneypat which worked like a charm and solved my issue!
The solution from Ricardo is a clever way to do it in M. If you want to do it in DAX, here is an expression you can use in a calculated column to get your desired result.
label = var currentworkdays = 'Fact'[workdays]return CALCULATE(MIN('Dimension'[label]), 'Dimension'[low]<=currentworkdays, 'Dimension'[high]>=currentworkdays)If this works for you, please mark it as the solution. Kudos are great too. Please let me know if it doesn't or if any questions.
Regards,
Pat
