topic Re: Need help to get count of resources in work order (Dataverse) - Field service in DAX Commands and Tips

Need help to get count of resources in work order (Dataverse) - Field service

dejadpower — Mon, 04 Mar 2024 11:57:23 GMT

Hi All,

I have a custom column - resource type to store "mason,painter , etc" in Bookable Resource table. An work order has multiple bookings

I need to summarize:

Work order table has multiple Resource booking rows which is from another table

each Resource has a category (mason, painter etc)

how to display count using measure or column?

anyone please guide

thanks for helping!

Re: Need help to get count of resources in work order (Dataverse) - Field service

olgad — Mon, 04 Mar 2024 12:14:08 GMT

Hi, in Power Query, duplicate the work order,

Select resource type-transform-pivot the column

Re: Need help to get count of resources in work order (Dataverse) - Field service

dejadpower — Mon, 04 Mar 2024 12:36:11 GMT

HI ''

thank you for the reply. I am a beginner in power bi. can you please elaborate ? I need to display other columns also from the work order table, is it possible to combine work order and pivoted table content for selected customer?

Re: Need help to get count of resources in work order (Dataverse) - Field service

Anonymous — Tue, 05 Mar 2024 06:27:02 GMT

Hi dejadpower ,

Where are you stuck? I think it is working. Please share your sample data and show your expected results.

Best regards,
Community Support Team_ Scott Chang

Re: Need help to get count of resources in work order (Dataverse) - Field service

dejadpower — Tue, 05 Mar 2024 07:52:40 GMT

Hi Anonymous ,

thanks for the support. I am sharing the report data here

please guide to achieve the desired result.

Re: Need help to get count of resources in work order (Dataverse) - Field service

olgad — Tue, 05 Mar 2024 08:11:05 GMT

In power Query, merge the booking table with the resource table based on the resource. expand , and bring the resource category into the bookings table. then pelase follow like i described before. My snapshots show you the same output that you would like to have.

Re: Need help to get count of resources in work order (Dataverse) - Field service

dejadpower — Tue, 05 Mar 2024 10:00:16 GMT

Thank you olgad .

I am an absolute beginner in power bi DAX. Is it possible to give me any DAX expressions to achieve the steps you explained . I'm completely stuck , thanks for helping

Re: Need help to get count of resources in work order (Dataverse) - Field service

olgad — Tue, 05 Mar 2024 10:29:46 GMT

1. Step 1

2. Step 2
Select work order, select merge queries, select resource table, select resource column in both tables , click OK

Step 3.

Step 4 Right click on work order column, duplicate column

Step 5

If needed adjust the data types,
then file close and apply

If you want DAX and you have only 6 Resource types, then Create relationship between the table based on resource code. And then write 6 DAX Measures.
Painter=Calculate(Countrows(Worktable), Resource Type=1)
Mason= Calculate(Countrows(Worktable), Resource Type=2) etc. it is easier for the beginner, but not dynamic, in case you get more resource types than 6.

Re: Need help to get count of resources in work order (Dataverse) - Field service

dejadpower — Wed, 06 Mar 2024 06:03:38 GMT

after merge queries, i selected resource as matching column. it throws error . please help

Re: Need help to get count of resources in work order (Dataverse) - Field service

dejadpower — Mon, 11 Mar 2024 09:25:42 GMT

Thank you olgad

As i mentioned in the previous reply, i couldnt able to merge the table, it throws error.

what i decided is, I created a result table with all the required columns from both Work order booking and resource tables

and then added calculated columns for finding the category as below:

Result Table = SELECTCOLUMNS((NATURALINNERJOIN(BookingsWO,Resources)),"WO",BookingsWO[Work Order],"DESC",BookingsWO[Work Order Summary],"TYPE",Resources[Resource Category],"NAME",Resources[Name],"START",BookingsWO[Start Date],"END",BookingsWO[End Date],"JOB",BookingsWO[JobNoCbs],"CUSTOMER",BookingsWO[Service Account],"MASON",if(Resources[Resource Category]="MASON",1,0),"PAINTER",if(Resources[Resource Category]="PAINTER",1,0),"PLUMBER",if(Resources[Resource Category]="PLUMBER",1,0),"ELECTRICIAN",if(Resources[Resource Category]="ELECTRICIAN",1,0),"CARPENTER",if(Resources[Resource Category]="CARPENTER",1,0),"AC",IF(Resources[Resource Category]="AC",1,0),"HELPER",IF(Resources[Resource Category]="HELPER",1,0),"OTHERS",if((Resources[Resource Category]<>"MASON" && Resources[Resource Category]<>"PAINTER" && Resources[Resource Category]<>"PLUMBER" && Resources[Resource Category]<>"ELECTRICIAN" && Resources[Resource Category]<>"CARPENTER" && Resources[Resource Category]<>"AC" && Resources[Resource Category]<>"HELPER"),1,0))

Then in the table Visual, i chose all these columns from result table, Start Date as Earliest Start Date, End Date as Latest End Date.

this give me the desired output. But the total of date difference is wrong.

the total days should be 22 , but it shows the entire difference from earliest start and latest end dates.

Please guide if Is there any way to solve it? Is there any disadvantages in this method instead of power query merge?

thank you for your time