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

[Anonymous]

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.

[wdx223_Daniel]

@Anonymous please try this M code

[Anonymous]

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

Thanks for the assistance.

[wdx223_Daniel]

@Anonymous you miss a _ before {0}

[Anonymous]

@wdx223_Daniel   added that in but still 3 columns of Error

[wdx223_Daniel]

@Anonymous 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}}))

[Anonymous]

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

[wdx223_Daniel · Accepted solution]

@Anonymous 

= #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]}))

[Anonymous]

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

[Anonymous]

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

[wdx223_Daniel]

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

[Anonymous]

@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

[wdx223_Daniel]

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

[Anonymous]

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

[Bohumil_Uhrin]

@Anonymous, @wdx223_Daniel 

could you please provide copiable input / output?