Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 please try this M code
= #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.
@Anonymous you miss a _ before {0}
@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}}))
@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.
@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]}))
@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?
@Anonymous 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. @Anonymous
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.