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

Filter to show data at aggregate level not row level

Hi,

 

I have a dax measure for total duration. Right now its a filter on a table visual which has user name & Activity & total duration as fileds . So when I Filter the table with saying duration > 30 mins, it filters all users & activity which have duration > 30 mins at row level. What I want to do is, I want to calculate the duration on activity level not activity per user level. 

What I have 

User NameActivityduration
U1meetings 1 hr
U2meetings20 mins 
U3non-meetings 

35 mins

   

 

So when i apply the filter of duration > 30 mins, it shows me U1 & U3, but I want my selection to also show U2, since meetings duration combined for U1 & U2 is > 30 mins.

Also, tabular representation above is a simple way of giving this example, I'm using other visuals on this and not just tabular. So need a solution that would work irrespective of the visual selected.

1 ACCEPTED SOLUTION

hi @newpi 

You could just create a measure as below and add it into visual level filter then set filter is > 30.

To filter = CALCULATE(SUM('Table'[duration]),FILTER(ALLSELECTED('Table'),'Table'[Activity]=MAX('Table'[Activity])))

8.JPG

 

and here is sample pbix file, please try it.

 

by the way, you could also use ALL instead of ALLSELECTED in this measure.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@newpi , You have to try something like

averageX(filter(summarize(Table,Table[User Name],"_1", sum(Table[duration])), [_1]>30),[_1])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak This does not seem to work. Also, duration is a measure for me not a column so can't use the sum function and had tried with sumx .

hi @newpi 

You could just create a measure as below and add it into visual level filter then set filter is > 30.

To filter = CALCULATE(SUM('Table'[duration]),FILTER(ALLSELECTED('Table'),'Table'[Activity]=MAX('Table'[Activity])))

8.JPG

 

and here is sample pbix file, please try it.

 

by the way, you could also use ALL instead of ALLSELECTED in this measure.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Greg_Deckler
Super User
Super User

@newpi - You cannot use a measure in a Page level filter.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

edited my title. Can you review the post again please

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!