cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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 IDbegindateenddateworkdays
tk00011-1-20202-1-20202
tk00022-1-20204-1-20202
tk00033-1-20206-1-20202
tk00044-1-20208-1-20203
tk00055-1-202010-1-20205
tk00066-1-202012-1-20205
tk00077-1-202014-1-20206
tk00088-1-202016-1-20207
tk00099-1-202018-1-20207
tk001010-1-202020-1-20207
tk001111-1-202022-1-20208

 

Dimension table layout:

labellowhigh
<1 workday01
1 workday12
2 workdays23
3 workdays34
4 workdays45
1 week510
2 weeks1015
3 weeks1520
4 weeks2025

 

How would i combine the 2, to create a column in the fact table with the values mentioned in the dim table?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

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




Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Super User III
Super User III

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")

 

Capture.PNGCapture 1.PNG

 

Did I answer your question? Mark my post as a solution!
Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Super User III
Super User III

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




Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

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

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors