Need a little help with Slicers and Relationships

[Anonymous]

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

[DataZoe]

@Anonymous 

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-items-with-data

[Anonymous]

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 · Accepted solution]

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

[Anonymous]

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

[DataZoe]

@Anonymous 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. 🙂

[Anonymous]

Really apprecitate the help!

- T