Power Pivot, help with binning / grouping

[decarsul]

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?

[mahoneypat · Accepted solution]

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

[camargos88]

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!
Ricardo

[decarsul]

@camargos88 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!