Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Microsoft Employee
Microsoft Employee

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
Microsoft Employee
Microsoft Employee

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,
Zoe Douglas (DataZoe)



Follow me on LinkedIn at https://www.linkedin.com/in/zoedouglas-data
See my reports and blog at https://www.datazoepowerbi.com/

mahoneypat
Microsoft Employee
Microsoft Employee

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


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors