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

Unsure how to tie this together with Power Query (multiple MAXIFS)

Hopefully I can explain this clearly 🙂

 

I am trying to come up with a Matrix visualisation that will show the current open positions we have.

 

In my data model I have the crew plan, which shows the artists crafts, per week across a whole production and the total people we expect to have that week.

I have another table which shows the current crew onsite, per craft also.

What I am trying to do is derive the max for each crew from the crew plan, and then deduct the current number of crew for that craft to come up with the hire number.

I have worked this out in Excel using Maxifs to determine the correct information, but I am not sure how to perform the same thing in Power Query in Power BI to display the data in any sort of format really.

 

Below is how I ended up with the 'Open' number by using some mockup tables in Excel, but for the mass crew plan in Power BI the concept is the same.  There is craft data, dates and counts for the next 3 years.  The Customer table is basically out AD dump to Cherwell which I have filtered for Active users, in the data model these are connected via Site and Craft DIM tables to allow reporting between the tables.

I am just not sure how to come up with the difference total and the month where the max number of crew needed happens.

CrewLevels.JPG

1 ACCEPTED SOLUTION

 

@stevenls 

 

= #table({"Craft","Max_Count","PeakMonth","PositionsToFill","Location"},Table.ToList(ActiveStaff, each let a=Table.Sort(Table.SelectRows(CombinedCrewPlans,(x)=>x[Craft]=_{0}),{"Count",1}){0}? in if a=null then {_{0},null,null,null,null} else {_{0},a[Count],a[Date],a[Count]-_{2},a[Location]}))

 

View solution in original post

14 REPLIES 14
wdx223_Daniel
Super User II
Super User II

@stevenls please try this M code

wdx223_Daniel_0-1603858910603.png

 

@wdx223_Daniel 

 

 

= #table({"Craft","PeakMonth","PositionsToFill"},Table.ToList(ActiveStaff, each let a=Table.Sort(Table.SelectRows(CombinedCrewPlans,(x)=>x[Craft]={0}),{"Count",1}){0}? in if a=null then {_{0},null,null,null} else {_{0},a[Count],a[Date],a[Count]-_{1}}))

 

Above is what I have in place, and using the actual data information but it tanks out.  Maybe I am referring to something incorrectly.

 

Below are the table nows from the Power BI datamodel as well as some sample data to show the column names also.  Have I mucked up a reference?

TableIssue.jpg

Thanks for the assistance.

@stevenls you miss a _ before {0}

wdx223_Daniel_0-1603932621613.png

 

@wdx223_Daniel   added that in but still 3 columns of Error

@stevenls you still miss a field name of Max_Count

= #table({"Craft","Max_Count","PeakMonth","PositionsToFill"},Table.ToList(ActiveStaff, each let a=Table.Sort(Table.SelectRows(CombinedCrewPlans,(x)=>x[Craft]=_{0}),{"Count",1}){0}? in if a=null then {_{0},null,null,null} else {_{0},a[Count],a[Date],a[Count]-_{1}}))

 

@wdx223_Daniel  wow thanks, that was a pretty big miss.


I still have 'Error' in the PositionsToFill column.  Also, if I wanted to add Location to this table from CombinedCrewPlans where do I add that in.  Struggling to understand the column populations with this one, but am trying to disect the command.  Appreciate your assistance with this one, it is a great learning exercise for me seeing this unfold.

 

S.

 

@stevenls 

 

= #table({"Craft","Max_Count","PeakMonth","PositionsToFill","Location"},Table.ToList(ActiveStaff, each let a=Table.Sort(Table.SelectRows(CombinedCrewPlans,(x)=>x[Craft]=_{0}),{"Count",1}){0}? in if a=null then {_{0},null,null,null,null} else {_{0},a[Count],a[Date],a[Count]-_{2},a[Location]}))

 

View solution in original post

@wdx223_Daniel  amazing thanks.  I have made a mess of location somewhere as it isn't splitting out the sites for Sydney and Vancouver but I will figure that one out.

 

Greatly appreciate the time spent with this one.

 

Cheers.

@wdx223_Daniel  coming unstuck with the location side of things.

 

The ActiveStaff shows the current count per NSW or BC (the location).  I have a dim table with two columns LocationLong, LocationShort which Maps Sydney to NSW and Vancouver to BC.  I have this all the way through tables due to the differing sources so that should be fine.

The CombinedCrewPlan shows two maximum values for each craft. For Animation the Max is 49 - 31/05/21 (Sydney) and 59 - 01/03/21 (Vancouver).

The output is showing two lines for Animation with the correct PositionsToFill number, but the PeakMonth and Max_Count are only showing for Vancouver - so 59 on both lines and 01/03/21.  Should the location be coming from the ActiveStaff table instead of the CombinedCrewPlan one?

 

@stevenls could you provide some sample data, and show what result you want?

@wdx223_Daniel  There are three tabs,  the first two represent the tables and the last is the output

 

https://drive.google.com/file/d/1xTcgKcBt3IiLsfukLtgGaETlto7G8HDp/view?usp=sharing

sorry, i can not reach the file you provide. @stevenls 

@wdx223_Daniel  message me your email address and I will flick it over to you.

@stevenls@wdx223_Daniel 

could you please provide copiable input / output?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors