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

Get 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

Reply
-Thomas-
Helper I
Helper I

Need a little help with Slicers and Relationships

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:

  • I have an Opportunity table with a OwnerID.
  • I have a User table with UserID.
  • I have created a one way 'One to Many' relationship from UserID to OwnerID.
    (I have also tried a 'Many to One' relationship from OwnerID to UserID)

My Page Setup:

  • I have a page filter (and visual) that only show opportunities from this Quarter.
  • I have a Slicer with the User table's 'Full Name' in the Slicer Field.

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

1 ACCEPTED SOLUTION
DataZoe
Microsoft Employee
Microsoft Employee

@-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":

 

DataZoe_1-1600355696619.png

 

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/

View solution in original post

6 REPLIES 6
DataZoe
Microsoft Employee
Microsoft Employee

@-Thomas- 

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

DataZoe
Microsoft Employee
Microsoft Employee

@-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":

 

DataZoe_1-1600355696619.png

 

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?

 

2020-09-17 08_25_01-Salesforce Accounts & Opportunities - Power BI Desktop.png

 

 

 

 

 

 

 

 

 

 

 

Thank you so much.

T

 

 

DataZoe
Microsoft Employee
Microsoft Employee

@-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 :

DataZoe_0-1600365262986.png

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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