Count resources on bench

[dsj8wksnnckk]

Hi,

I need help with counting people on bench.

I have the following tables:

Table1 [Booking]
with columns:
Resource Name, Project Type, Status Name, Start Date, Duration

Table2 [Resources]
Resource Name, Tech Stack, Seniority, Status

I need to count resources on bench for following months
A resource is on bench if has NO Duration tracked for certain Project Type criteria

I need a matrix that will show me Tech Stack, Seniority, Resource and respective count of bench as drilling down.

I've tried several approaches and so far no luck. 

Any Ideas?

[dsj8wksnnckk · Accepted solution]

The Billing Type is a Calculated Column using data on Project and additional dimensional table containing more detailed information on Project.

As you can see, there are multiple instances of Duration in 1 day.

Definition of bench: a Resource is on bench if it has NO duration for Status Name = Hard && Project Type = Commercial && Billing Type = Chargeable

[dsj8wksnnckk · Accepted solution]

I resolved this:

CountOnBench =

SUMX(

    VALUES('Resources[Resource Name]'),

    [IsOnBench]

)

IsOnBench =

IF(

    [Allocation] = 0,

    1, BLANK()

)

[v-jtian-msft]

Hi,@dsj8wksnnckk ,I am glad to help you.

According to your description, you want to filter records related to projects with a null value for Duration based on the status of the Duration of the project.

This includes tech stack, seniority, resources and corresponding bench counts.

I ran the following test and presented the results as a table of calculations (I also created the corresponding measure)

The results can be seen by placing the table of calculations in a matrix

Here is my test data

Based on your description, you want to filter the records related to the project's Duration with a null value based on the status of the Duration.

This includes tech stacks, seniority, resources and corresponding bench counts.

I ran the following test and presented the results as a table of calculations (I also created the corresponding measure)

The results can be seen by placing the calculate table in a matrix

Here is my test data
Booking :

The code of calculate column C_dur:

C_dur = IF(ISBLANK('Booking'[Duration]),0,1)

Resources:

Calculate table created:

The final result is as follows.

Could you give me some specific data that is not private, that would be very helpful in solving your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dsj8wksnnckk]

Hi @v-jtian-msft,

thanks for your assistance. I am uploading additional screenshots - did in excel:

This last one is mimicking the matrix visual with drill down. 
Additionally, filtering by project type is also needed, I didn't do an example of that. 

In the Booking there is no null field for Duration, it has to be compared to the existing Resources with Active status (as someone might be an active resource but had no booking - therefore is on bench)
Also, soft bookings, and some project types don't count (non commercial projects, internal, etc) so they also must be excluded. 

[v-jtian-msft]

Hi,@dsj8wksnnckk 
I'm glad to help you, according to your description I've created the corresponding test data
below:
Booking:

Resources:

I've tried to reproduce your screening requirements through the drilling simulation results you gave me.

Here is my personal understanding, if it does not meet your requirements, please do not hesitate to get back to me.

There are two main filtering criteria:

1. Filter the data from the "Resources" table by "Status" with status="Active".
2. From the "Booking" table, according to "Status Name", filter out the data whose name is not equal to "Hard", or is The "ResourceName" in the "Resources" table does not exist in the "Booking" table, and then filter out the corresponding data.

If the above two conditions are met, then record the "MonthYear" of the target data in the "Booking" table and display it in the matrix.

Here is the test result:

created Measure: M_

M_ =
VAR res_Name =
    CALCULATETABLE ( VALUES ( 'Booking'[Resource Name] ), ALL ( Booking ) )
VAR _name =
    SELECTEDVALUE ( Resources[Resource Name] )
VAR user_notHard =
    CALCULATE (
        COUNTAX ( 'Booking', SELECTEDVALUE ( 'Booking'[MonthYear] ) ),
        FILTER ( 'Resources', 'Resources'[Status] = "Active" ),
        FILTER ( 'Booking', NOT ( 'Booking'[Status Name] = "Hard" ) )
    )
VAR user_noProject =
    IF (
        NOT ( SELECTEDVALUE ( 'Resources'[Resource Name] ) IN res_Name )
            && SELECTEDVALUE ( 'Resources'[Status] ) = "Active",
        1,
        BLANK ()
    )
RETURN
    SWITCH (
        TRUE (),
        _name IN res_Name, user_notHard,
        NOT _name IN res_Name, user_noProject
    )

Here's my explanation of M_.
VAR res_Name=CALCULATETABLE(VALUES('Booking'[Resource Name]),ALL(Booking))
Res_Name: in the case of removing all other external filters (to avoid the impact of the matrix), filter out all the ResourceName in the Booking table
VAR _name = SELECTEDVALUE(Resources[Resource Name])
_name: get the current value of the sourceName to be judged
The following is to carry out judgment, if it is the first case: in the "booking" table to find the corresponding resourceName, filter out the active and
Booking'[Status Name] is not equal to "Hard" data.
If it is the second case: if you can't find the corresponding resourceName in the "booking" table, filter out the active records and customize the value displayed on the Matrix to 1, otherwise it will be empty.

Below is my question: if you could reply to me with a more detailed explanation, that would be helpful in solving your problem.
question：(as someone might be an active resource but had no booking - therefore is on bench)
Also, soft bookings, and some project types don't count (non commercial projects, internal, etc) so they also must be excluded. 

I do not understand the meaning of these elements, can you make it clearer, it would be nice to see some non-sensitive data

Thanks for getting back to me.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dsj8wksnnckk · Accepted solution]

The Billing Type is a Calculated Column using data on Project and additional dimensional table containing more detailed information on Project.

As you can see, there are multiple instances of Duration in 1 day.

Definition of bench: a Resource is on bench if it has NO duration for Status Name = Hard && Project Type = Commercial && Billing Type = Chargeable