Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    5 years ago

     

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

     

14 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.