Forum Discussion

andris_'s avatar
andris_
Resolver I
9 years ago
Solved

data filtering

Hey everyone,   I have a seemingly simple problem, but I just can't deal with it. We have a table which contains service orders, with a lot of columns, with service order codes, types, names of the...
Need Help
andris_'s avatar
andris_
Resolver I
9 years ago

 

 

Greg_Deckler


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here's some sample data. The output will be a table, which will show aggregate values, like aggregate work hours for every technician. But, we'd like to filter out from the aggregation those work hours (a day) where work hours<1. So, like in the sample, we wouldn't like to summarize for Name1 the work hours from 01.02 when he worked less than an hour.

 

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

So, I am assuming that 1,02 is one hour and 2 minutes or is that .02 of 60 minutes?

 

Also, on 1.6.17 for Name1 would you want to the 024 Order No but not the 023 Order No or would you would you count those both together because the total is over 1 hour.

  • andris_'s avatar
    andris_
    Resolver I
    9 years ago

    1,02 hour is one hour and 0,02 of 60 minutes.

     

    And, I'd like to count those together. If the aggregation of them were less than 1 hour, I wouldn't like to show them in my table.

     

    It's a bit complicated, but we're doing it to filter those days per technician, where the technians were on holiday. Because if they are on holiday, they still can order something on their phone in the technician mobile system, and it generates a service order. And when we count his work time utilization, and it's wrong cause we divide the working hours by 8, and it will be low of course, so we'd like to ignore them. I posted about it a week ago, but we couldn't solve it (http://community.powerbi.com/t5/Desktop/date-holiday-issue/m-p/127507#M54145), so we figured it out that if technicians are on holiday, they usually do maximum of 3 or 4 service orders, and these working hours are always lower than 1 hour. (if they work, they always work more than 1 hour of course) So, that's why we'd like to filter those days.

     

    • danrmcallister's avatar
      danrmcallister
      Resolver II
      9 years ago

      This might be overkill, but give this a shot:

       

      Daily Hours per Worker Not Holiday = if(
	Calculate(
    Sum(Summary[Working Time (Hours)]),
      Filter(Summary,Summary[Technician] = Earlier( Summary[Technician] )
         ),
         Filter(Summary,Summary[Posting Date].[Date] = Earlier(Summary[Posting Date].[Date])
             )) > 1,
			 	Calculate(
    				Sum(Summary[Working Time (Hours)]),
     					Filter(Summary,Summary[Technician] = Earlier( Summary[Technician] )
       					  ),
       					  Filter(Summary,Summary[Posting Date].[Date] = Earlier(Summary[Posting Date].[Date])
            				 )),
							 0
)

      Essentially it's saying "If the sum of worked hours for this technician on this date >1, give this value, else give 0".  If you look at the numbers in your full table where a worker has submitted multiple orders, the sum will repeat.  So I'm not sure we're quite there yet... seems like you would need another table to pull summary data into by date/technician.  Let me know what you think.