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 IV
Super User IV

@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 IV
Super User IV

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




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

Proud to be a Super User!




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.

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
Top Kudoed Authors