Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
dsj8wksnnckk
Resolver I
Resolver I

Count resources on bench

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?


2 ACCEPTED SOLUTIONS

 

dsj8wksnnckk_0-1715759719576.pngdsj8wksnnckk_1-1715759731402.png


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

View solution in original post

dsj8wksnnckk
Resolver I
Resolver I

I resolved this:

CountOnBench =
SUMX(
    VALUES('Resources[Resource Name]'),
    [IsOnBench]
)

IsOnBench =
IF(
    [Allocation] = 0,
    1, BLANK()
)


View solution in original post

5 REPLIES 5
dsj8wksnnckk
Resolver I
Resolver I

I resolved this:

CountOnBench =
SUMX(
    VALUES('Resources[Resource Name]'),
    [IsOnBench]
)

IsOnBench =
IF(
    [Allocation] = 0,
    1, BLANK()
)


v-jtian-msft
Community Support
Community Support

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 :

vjtianmsft_0-1715660028337.png


The code of calculate column C_dur:

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

Resources:

vjtianmsft_1-1715660083478.png

Calculate table created:

vjtianmsft_2-1715660106477.png

The final result is as follows.

vjtianmsft_3-1715660126513.png

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.

 

Hi @v-jtian-msft,

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

dsj8wksnnckk_0-1715673216664.pngdsj8wksnnckk_1-1715673229630.pngdsj8wksnnckk_2-1715673242704.png

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. 

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

vjtianmsft_0-1715751777018.png

Resources:

vjtianmsft_1-1715751820098.png

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:

vjtianmsft_2-1715751847342.png

vjtianmsft_3-1715751854664.png

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.

vjtianmsft_4-1715752070478.png

vjtianmsft_5-1715752083127.png

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_0-1715759719576.pngdsj8wksnnckk_1-1715759731402.png


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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.