Limiting Filter Options

[JM_nxgn]

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.

[Anonymous]

adding to that what will be the max number of report filters we can use in dashboard?

[Mariusz]

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.

[JM_nxgn]

@Mariusz 

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!

[Mariusz · Accepted solution]

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.

[JM_nxgn]

@Mariusz 

Hi I just noticed now what you said is true. I made a table with the two columns and does exactly as what you mentioned. 

I don't know why this isn't the case with the table I have created though. I'm using report level filters on the right side bar if that makes any difference?

[Mariusz]

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.