Updating slicers & bi-directional relationships

Power BI Data Model
1

Hi all,

I’m very new here so I’m sorry if this question has been raised and answered before.

I’ve been using Power Bi professionally for maybe 6 months. In my role in my organisation, we typically need a lot of slicers at the top of the page to enable our end users to drill down to their specific data. These slicers are always built from Look-up tables but slicers often come from different (unrelated) look-up tables.

Personally, I like to see a slicer update to only show the possible values after selecting an option from a different slicers. For example, if I select Export from one slicer, I should only see countries which we export to in a different slicer and not all countries in the model.

The only way I have been able to achieve this is by changing Cross Filter Direction to Both for the relationships for Countries to Fact and for SaleType to Fact. Basically, to make the model work the way I want it to, I could end up with many relationships being filtered in both directions.

For the most part, this seems to work effectively enough but having listened to Sam on one of the courses, I’m pretty sure this is not best practice. Sam says that he hardly ever uses bi-directional relationships so I’m wondering what is the work around in my scenario.

I realise that my above example may not be the clearest so I’ll give one relating specifically to my work context.

Fact table - results of students in various exams
Look-up 1 - Various schools in the organisation (possibly 10 options)
Look-up 2 - Various types of exams (possibly 10 options)

Scenario - I click a school name in a slicer but not all schools take all the various exams. I want the Exams Slicer to only display the exams relating to the school I just selected, and vice-versa.

Question - Is bi-directional relationships the only way to achieve this and if so, what issues am I going to run into as a result?

Sorry for the long-winded description. Really enough the courses I’ve taken so far. I’ve been a big fan on YouTube for a while so I’m delighted to be a part of this community now.

Kind Regards,

Sean

2

Have you tried messing around with the Edit Interactions? Click on a slicer, Format–>Edit Interactions and selec the other visuals that should or should not be filtered.

Not 100% that will work, but worth a shot since it’s quick. If it doesn’t, can you post some sample data with your goal output?

1 Like
3

@seancasey,

Welcome to the forum!

Funny – I just ran into this exact same problem yesterday on a report I’m working on. I would definitely endorse @Nick_M’s approach as the one with no significant downside, and thus the one to try first.

However, if you can’t get that to work in your model, the approach I had to take was to denormalize my lookup tables. So in your case, that would involve combining the School and Exam tables into one. This will cause some duplication/bloat in your lookup table, but will make the slicers operate as you want them to, and keep the relationships one-way. Definitely preferable IMO to bidirectional relationships, which can result in unexpected/unpredictable results and make DAX much more difficult to debug.

  • Brian

P.S. Here’s a major deep dive article on the perils of using bidirectional relationships to sync slicers…

TL;DR - don’t do it!

4 Likes
4

Thanks for getting back to me @Nick_M,

I’ve tried something like this in the past and it just didn’t get me to where I wanted the model to be. Having read the article from @BrianJ below, I think its better to sacrifice my desired functionality in favour of building a model that works all the time as opposed to most of the time.

Many thanks again for your reply.

Sean

5

Thanks for this @BrianJ, an interesting read.

It was only last week when I commented to my boss that I needed to figure out why I kept getting this ambiguity errors when creating relationships. I know see why this was happening. In the style of the model Sam suggests in his training videos, I had all my look-up tables on the top row and my fact(s) table underneath them. Due to my desire to have slicers updated based on the interactions with other slicers, the vast majority of my relationships where bi-directional. Once the models started to grow, then so did the issues.

I’ll try out your approach to denormalising some of my look-up tables but like you said, I can see the size of the model exploding very quickly as I begin to incorporate additional datasets on behaviour, attendance, standardised assessments, etc.

As I’m typing this, however, I’m beginning to think of additional work-arounds to help people in only clicking on information relevant to their situation, like that in the article you shared. The slicer might not update but there will be a way of presenting the various options to the end-user through visuals and tables. It won’t stop everyone selecting an option with no underlying data but it might stop the majority from doing so.

Thanks again for your reply. I’m looking forward to exploring this forum in a little bit more detail over the next few months.

All the best,

Sean

6

Hi Sean,

Just a question for you, have you considered using the Filter pane instead of page level Slicers?

This can also have a positive effect on performance…

7

This may or may not be an issue. VertiPaq is pretty amazing compression technology. If you want, upload some sample data( as you have it now) and we can work on making it work how you would want w/o, hopefully, a performance hit

8

Hi @seancasey, we’ve noticed that no response has been received from you since December 03, 2019. We just want to check if you still need further help on this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arises.

9

Hi @seancasey, a similar topic was covered in this forum thread link. You might get additional tips here