cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
andris_ Regular Visitor
Regular Visitor

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 workers, posting dates, working times etc. We're trying to filter the rows where the work hours on a day for a worker is less than 1 hour. We can do it when we create a table ordered by the workers' name and with a simple daily date filter using a simple visual level filter(Work hours' value is greater than 1), but of course when we try to create a monthly summarized table, it's not the same, because if we set the same filter, it won't summarize the work hours a day, so it would filter every order which lasted less than an hour.

 

So, my question is that can we create a measure which summarizes the work hours per day, so we could use a visual level filter which checks whether the work hours a day is less than 1?

 

(I tried to create a measure with the Calculate and Sum functions, like

Workhour a day = CALCULATE(SUM(Table[WorkHours]);...)

but I didn't know what should the filters be in the Calculate function.)

 

Thanks in advance,

Andris Szepesy

1 ACCEPTED SOLUTION

Accepted Solutions
andris_ Regular Visitor
Regular Visitor

Re: data filtering

hey @danrmcallister,

 

Thanks for your answer, I think it is good, just little bit complicated. Someone helped me in my other post, and it seems like that it's working, it's simpler a little bit than your code, take a look at it: https://community.powerbi.com/t5/Desktop/filtering-days/m-p/128948#M54799

View solution in original post

7 REPLIES 7
Super User
Super User

Re: data filtering

You will need to supply some sample data and an example of what your expected output is.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

andris_ Regular Visitor
Regular Visitor

Re: data filtering

 

 

@Greg_Deckler


kep.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

 

 

Super User
Super User

Re: data filtering

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

andris_ Regular Visitor
Regular Visitor

Re: data filtering

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 Regular Visitor
Regular Visitor

Re: data filtering

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.

 

PBI Summary excepting values.jpg

 

 

 

 

andris_ Regular Visitor
Regular Visitor

Re: data filtering

hey @danrmcallister,

 

Thanks for your answer, I think it is good, just little bit complicated. Someone helped me in my other post, and it seems like that it's working, it's simpler a little bit than your code, take a look at it: https://community.powerbi.com/t5/Desktop/filtering-days/m-p/128948#M54799

View solution in original post

danrmcallister Regular Visitor
Regular Visitor

Re: data filtering

Makes sense, thanks for the link!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 54 members 1,208 guests
Please welcome our newest community members: