cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newpi
Helper V
Helper V

Need help with filters & conditions

I have a table which has user name, application name, time(seconds) I want to create a table where if the cumulative duration >30 mins then it should shows me values for that application.

 

Eg:

MY Data

User NameApplication Nametime 
U1slack10 mins (600 secs)
U2slack25 mins
U3 MS Office25 mins
U6 MS Office3 mins
U4spotify15 mins
U5Spotify35 mins

 

When I apply my condition of duration > 30 mins , then the Output view should be

 

User NameApplciationtime
U1slack10 mins (600 secs)
U2slack25 mins
U4spotify15 mins
U5Spotify35 mins

 

 

U3 & U6 did not make the cut because total time for MS Office was only 25+3 mins which is less then my min of 30 mins in the filter or wherever I can apply that condition. 
Right now I cant apply my condition of time > 30 mins  in the filters because its checking for time at row level meaning by user & application and its only showing me U5 in the output which is > 30 mins.

 

I would prefer not creating a new calculated column as it increases my file size. What I shared here is only example. I have millions of rows of data. But if there is no other way around then I can do that.

I have created measures for time(seconds) to convert it into mins & hours so can use that as well.  

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

Create a measure like the one below and use it as a visual filter in the filter panel on your Application Name column (for >30).

 

Total App Time = CALCULATE(SUM(Table[Time]), ALLSELECTED(Table[User]))

or

Total App Time = CALCULATE(SUM(Table[Time]), ALLEXCEPT(Table, Table[Application Name]))

 

Replace Table with your actual table name.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
DataZoe
Super User II
Super User II

Hi @newpi ,

 

You could use this measure:

 

Total Time per Application = CALCULATE(sum('My Data'[Time]),all('My Data'[User Name]))
 
ttpa.JPG

 

 
Then, at the visual level, filter it to > 30:
 
ttpa_filter.JPG

Respectfully,
DataZoe


See my reports and blog at https://www.datazoepowerbi.com/

mahoneypat
Super User IV
Super User IV

Create a measure like the one below and use it as a visual filter in the filter panel on your Application Name column (for >30).

 

Total App Time = CALCULATE(SUM(Table[Time]), ALLSELECTED(Table[User]))

or

Total App Time = CALCULATE(SUM(Table[Time]), ALLEXCEPT(Table, Table[Application Name]))

 

Replace Table with your actual table name.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors