Filtering on totals - Major Power BI Flaw???

[schwinnen]

I have tried multiple times to get this resolved with no success, so I am going to try one more time.  It is something so basic and, for me, so necessary that I cannot believe it is not only possible, but easy.  Below is a link to a file showing a very simplified version of what I need to accomplish.

https://1drv.ms/f/s!Am--E_G2VxhMg9gKVT1RZibIMhimAQ

All I would like to do with this file is create a slicer that will allow me to filter on aggregated data, such as showing only customers over X number of orders or only customers over X dollars in sales.  I need this slicer to work with other slicers and I need it to work with filters on my reports.  

To me, this is such a basic function that I cannot understand why it is so difficult.  I would probably use it for every single report I do.  I have spent entire days trying to figure this out

Any help would be greatly appreciated.

[Anonymous]

Definitely not impossible, just need to use Parameters in Power BI and set filters based on those parameters.

I've built a quick solution that does this, but I don't have write access to that onedrive account.  Can you make that folder public (anyone can edit)?

[schwinnen]

https://1drv.ms/f/s!AG--E_G2VxhMbw

I had to move them to another folder to make it public.  Hopefully I did it right.  I've never made a onedrive folder public before.

[Anonymous · Accepted solution]

Weird, can't upload a file there.

Oh well, use this link.  Public folder.

OneDrive Folder

Anyway, search for "Power BI Paramters", you'll find a slew of information.

Also, Rob Collie used to call this trick a "harvesting measure".  You'll see mention of the same thing on his blog at www.powerpivotpro.com

Cheers,

~ Chris

[schwinnen]

This is brilliant, by the way.  Really saved the day.  I have posted 3 messages about this in the community and spent days trying to figure it out.  If I could buy you a virtual beer somehow, I would gladly do it.

[schwinnen]

@Anonymous, do you know if I am able to use parameters to filter on a bar chart?  I am working with the parameter as you showed me and it works great to filter a table, but I'm not able to get it to filter the bar chart.  I can work with it either way, but filtering the bar chart with the parameter would be ideal.

[Anonymous]

@schwinnen, should be able to with no issue.  All Power BI visuals are essentially tables of information, with HTML/CSS applied to make them look like a graph.  

Are you sure you're using the new measures that "listen" to the parameter that you select via the slicer?

[schwinnen]

I figured out what is going on.  I am using the parameter you showed me to display locations with a minimum number of LOADS.  However, I am not using LOADS as a value in my graph.  My graph is average time at a location and I want to filter to only show locations with a certain number of records or LOADS.  Even if I add LOADS as a value to my chart so that I have two bars (one for average time at location and one for LOADS), then use the parameter, all of the locations will still be displayed, but the bar for number of LOADS will disappear for those locations under the parameter value.  

This is not a huge deal.  I came up with a workaround where I created a table of locations and number of LOADS.  I then use the parameter to filter this table.  The user can then just click on the location in the table to filter the graph.  This is not quite as efficient as having the parameter control filter the graph, but it works.

[schwinnen]

I believe I am but I will work on it more in a little while.  The interesting thing was that I added that new measure to the tooltip on the bar graph.  When I used the parameter, it did not filter out the data outside of the parameter, but any bar that did not fit inside the parameter no longer displayed that measure on the tooltip.  If it should work, I will figure out what I am doing wrong.

[schwinnen]

I probably did something wrong when sharing that folder.  Anyway, parameters seems like a great solution.  I use a parameter for standard deviations for another report so I should have thought of that.  Anyway, just switched over to Power BI from Tableau.  I like Power BI much better actually, but getting used to the differences and getting out of the mindset of how I used to do it is taking some time.  Great help, once again though!