Fixed and filtered data in table

[cnschulz]

Hi, 

I have an interesting issue that I am sure is simple but it has broken my brain.

I am writing a report showing jobs assigned to crews on a weekly basis. It is used for forecasting availability and takes into account leave. I have two tables both using WEEKNUMBER and CREWID to classify data. Its working fine (see below) but not I need to introduce JOB PRIORITY as a filter and this breaks my mind.

Week	Crew	Job Hours	Available Hours	Balance
202046	A	200	220	+20
202046	B	220	220	0
202046	C	250	190	-60

I need to introduce JOB PRIORITY to the JOB table and display the following table:

Week	Crew	Priority	Job Hours	Available Hours	Priority Balance	Overall Balance
202046	A	1	150	220	+70	+20
202046	A	2	50	220	+170	+20
202046	B	1	20	220	+200	0
202046	B	2	100	220	+120	0
202046	B	3	100	220	+120	0
202046	C	1	190	190	0	-60
202046	C	2	50	190	+140	-60
202046	C	3	10	190	+180	-60

So as you can see above, we should drop all priority 2 and 3 jobs for crew C or reallocate other staff. 

Next, we need to be able to filter on priority (ie "what if we did just priority 1 jobs?") The table should look like this:

Week	Crew	Priority	Job Hours	Available Hours	Priority Balance	Overall Balance
202046	A	1	150	220	+70	+20
202046	B	1	20	220	+200	0
202046	C	1	190	190	0	-60

Then theres the possibility of selecting *multiple* priorites that I havent even considered yet!!

Can anyone offer any advice on creating this structute? Much appreciated. 🙂

[v-yingjl · Accepted solution]

Hi @cnschulz ,

First you can create a custom column [Priority] in power query for JOB table, the whole query is like this, close and apply it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAxU9JRcgRiQ1MDpVgdNEF0MScgNsIiZmhAjKAzSNASiyC6NWCFQLFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, Crew = _t, #"Job Hours" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}, {"Crew", type text}, {"Job Hours", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type",{"Crew"},{{"Rows", each Table.AddIndexColumn(_, "Index", 1), type table}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Week", "Job Hours", "Index"}, {"Week", "Job Hours", "Priority"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Rows",{"Week", "Crew", "Priority", "Job Hours"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Week", Int64.Type}, {"Priority", Int64.Type}, {"Job Hours", Int64.Type}})
in
    #"Changed Type1"

Then create some calculated columns to get the expected output in JOB table:

Available Hours =
CALCULATE (
    SUM ( AVAILABILITY[Available Hours] ),
    FILTER ( ALL ( AVAILABILITY ), 'AVAILABILITY'[Crew] = EARLIER ( JOB[Crew] ) )
)

Priority Balance = [Available Hours] - [Job Hours]

Overall Balance =
[Available Hours]
    - CALCULATE (
        SUM ( JOB[Job Hours] ),
        FILTER ( ALL ( JOB ), 'JOB'[Crew] = EARLIER ( 'JOB'[Crew] ) )
    )

The final JOB table is like this:

Filter priority 1:

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

@cnschulz 

How is the model set up?

[cnschulz]

JOB table contains 

WeekNumber, CrewId, JobHours 

AVAILABILITY table contains

WeekNumber, CrewId and HoursAvailable 

I now need to introduce Priority to the JOB table which will make a many to one relationship and break summarisation.