Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Issue: My Power of BI Desktop Slicer is showing all Users in the entire system, rather than only showing the Users that are associated with the filtered Opportunity data.
My Current Model:
My Page Setup:
The problem is that the Slicer shows all names in the table instead of the handful of people that own the current list of filtered Opportunities.
What do I need to do to make that Slicer only show User's that are related to the filtered Opportunities on the page?
Thanks in advance,
T
Solved! Go to Solution.
@-Thomas- I don't mind going through the steps at all 🙂
If your table is Opportunity, what you can do is create a measure like this:
Opportunites = countrows('Opportunity')
Then on your slicer, open the filter pane and add that measure to the "Filters on this visual":
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/
The easiest is to include a measure filter on the slicer itself. You can't use a measure as a filter on a page or all report filter in the filter pane, but you can in the "Filters on this visual" section. In there, add in a measure such as [Opportunites] and filter it to be NOT BLANK.
This also listed in the docs on alternatives to bi-directional filtering (which will also solve it, but introduce a bunch of new problems!) https://docs.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering#slicer-item...
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/
Thanks @DataZoe , while that seems helpful, I'm brand new to Power BI.
If my table is 'Opportunity', what would my measure look like?
Something like this? Or, more complex using Values? Do I need to Count? Use IDs?
[MEASURE] = [Opportunity] <> BLANK
I've tried a few things, but none of them have worked. Would you mind going through the steps with me?
Thanks,
T
@-Thomas- I don't mind going through the steps at all 🙂
If your table is Opportunity, what you can do is create a measure like this:
Opportunites = countrows('Opportunity')
Then on your slicer, open the filter pane and add that measure to the "Filters on this visual":
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/
Genius!
That did the trick. Really appreciate your time.
I do have one last quesation if you're willing 😉 If the page is already being filtered by date (see below), and the Slicer visual says it's being filtered by that same date, why isn't this the default behavior?
In other words, the CloseDate in the screenshot below is really NOT affecting the visual, even though it's listed?
Thank you so much.
T
@-Thomas- Sure! I think I can explain this one. This is to do with the filter propogation (which is, how tables interact with each other using the relationships).
My guess is that your model looks like this :
If you look at the arrows on the lines, you can see that Users points towards Opportunity. And Date points towards Opportunity. This means that if you filter to a specific date range, it will also filter Opportunity. If you filter to a specific user, it will also filter Opportunity.
It also means that Opportunity cannot filter Users or Date tables as there is no arrow going the other way. So even if the Date table filter is affecting the whole page, including the Users slicer, there is no path for the Date table to filter Users.
This changes when you have the context of a specific measure.
If there is a measure, then power bi can say, oh to calculate this SPECIFIC measure, what do we actually use from these tables? Anything where the measure calculates as blank() doesn't typically show in a table for example. Measures also can't be used in the filter pane for the whole page or whole report, just individual visualizations.
Not sure I made the best sense, but if you look up filter propogation in Power BI or Analysis Services or Power Pivot, there are other poeple that may explain it better. 🙂
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/
Really apprecitate the help!
- T
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
89 | |
83 | |
75 | |
49 |
User | Count |
---|---|
142 | |
140 | |
110 | |
69 | |
55 |