cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
olik Regular Visitor
Regular Visitor

Slicers: Only show available values

Hi all,

 

What's the Power BI equivalent of Tableau's "show only available values" in a filter?

 

Main table:

DayAccountValue
1A0.1
2A0.2
3A0.3
1B0.4
2B0.5

 

Dates table: 

DayAlias
11
22
3Latest


Say I have slicers for Main[Account] and Dates[Alias] - I want the Dates[Alias] slicer to only show "1" and  "2" when I set the Main[Account]  slicer to "B".

 

This seems like a very basic feature so I'm sure it must be achievable somehow?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
nirvana_moksh Established Member
Established Member

Re: Slicers: Only show available values

Just change the filtering to 'Both' in the relationship side

 

Capture1.PNGRESULTCapture2.PNGRELATIONSHIP

View solution in original post

7 REPLIES 7
ThomasFoster Established Member
Established Member

Re: Slicers: Only show available values

If your data model is like your example then the slicers will cross filter. However, if you have two slicers each from a different dimension table connected both in a single filter relationship to a fact table they will not crossfilter.

 

This is because when one filter is applied it goes through the relationship to the fact table however as the other dimension table is on a single direction relationship the filter from the fact table will not propogate back up to the second dimension table.

 

The solution is either to have two way relationships between your dim and fact tables, this can have strange and unintended consquences so this is not advised. 

 

Or you can try change the model such as merging dim tables or denormalising them into the fact table.

nirvana_moksh Established Member
Established Member

Re: Slicers: Only show available values

I am not totally sure I understood your problem, but when I add two slicers - One for Account and one for Day I get the expected result (attached in the screenshot)

 

Capture1.PNG

 

Or did you mean do you wanted to add a slicer and then upon the slicer limit it to a few defined options?

olik Regular Visitor
Regular Visitor

Re: Slicers: Only show available values

Hmm, sound accurate - for me I have an extra Dates table that just has the distinct dates (i.e. one-to-many). Filtering for an Account in the main table will not back-propagate to the Dates table.

 

Does that mean the only solution is the very unclean approach of adding my date aliases ("Most recent" etc.) to my main table (and hence to millions of rows) instead of using a separate Dates table?

olik Regular Visitor
Regular Visitor

Re: Slicers: Only show available values

@nirvana_moksh: Sorry, turns out my example was too simple - I've updated the original question now!

Highlighted
nirvana_moksh Established Member
Established Member

Re: Slicers: Only show available values

Just change the filtering to 'Both' in the relationship side

 

Capture1.PNGRESULTCapture2.PNGRELATIONSHIP

View solution in original post

olik Regular Visitor
Regular Visitor

Re: Slicers: Only show available values

Ah, perfect - that does the trick!

nirvana_moksh Established Member
Established Member

Re: Slicers: Only show available values

I have worked with Tableau and I have loved it a lot, but Power BI is pretty awesome and you will surely find all functionality in it Smiley Happy

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 503 members 3,917 guests
Please welcome our newest community members: