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

[dejadpower]

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!

[dejadpower]

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

[dejadpower]

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?

[olgad]

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. 

[dejadpower]

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

[Anonymous]

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

[dejadpower]

Hi @Anonymous ,

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

please guide to achieve the desired result. 

[olgad]

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. 

[dejadpower]

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

[olgad]

Hi, in Power Query, duplicate the work order, 

Select resource type-transform-pivot the column