Forum Discussion
Limiting Filter Options
Hi I'm busy building reports which have numerous filters on many different columns.
Is there a way to limit the number of filter options based on a previous choice of filter.
I'll provide an example, consider the table below:
| Animal | Breed |
| Dog | German Shepherd |
| Dog | Chow |
| Cat | Siamese |
| Cat | Russian Blue |
| Dog | Chow |
| Cat | Siamese |
| Dog | Corgi |
So say I have two columns, one with the animal type and the other with the specific breed. If I have filters on both columns. How do i change the filters such that if I filter Animal to 'Dog' then the available options when I want to filter by Breed is limited to only 'German Shepherd, Chow, Corgi'?
Thanks
EDIT: I forgot to mention I'm using report level filtering. I have to use report level filtering for this, not sure if it makes a difference. I'm not using slicers at all, only the filters on the right bar.
Hi JM_nxgn
Please see the below solution.
1. Create a measure
_filter = INT( NOT ISEMPTY( Owner ) )
2. Use this measure as a filter on both sliners like below.
Second option is to create one dimension table that contains both Breed and Animal and link it to Owner on Breed ID, and this would be the best practice.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
6 Replies
Hi JM_nxgn
Not sure if I understand what you need, but if you have one table with both columns in than the behaviour should be as below.
Please let me know if that is what you expecting.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.Hi JM_nxgn
If you filter visual in the Filters Pane then you are filtering only the visuals display, it does not propagate to the model for some reason, however if you apply your filter on this page / all pages levels, then you will get the desired result as below.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Okay I think I've found the issue, just not too sure on what procedure to follow in order to fix this. Probably something to do with creation of dimension tables (which I'm not too familiar with).
So the table I mentioned in the first post was created by these three tables:
AnimalId Animal 1 Dog 2 Cat BreedId Breed a Chow b Corgi c German Shepherd d Siamese e Russian Blue OwnerId AnimalId BreedId 1 1 c 2 1 a 3 2 d 4 2 e 5 1 a 6 2 d 7 1 b But then I'm using the respective AnimalName and BreedName as the filters and not their respective Id's.
Would this be a cause as to why it's not limiting the filter options? How would i fix this?
Thank you!
Hi JM_nxgn
Please see the below solution.
1. Create a measure
_filter = INT( NOT ISEMPTY( Owner ) )
2. Use this measure as a filter on both sliners like below.
Second option is to create one dimension table that contains both Breed and Animal and link it to Owner on Breed ID, and this would be the best practice.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
