Forum Discussion

JM_nxgn's avatar
JM_nxgn
Helper I
7 years ago
Solved

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 examp...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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.
    Mariusz Repczynski

     

Mariusz's avatar
Mariusz
Community Champion
7 years ago

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.
Mariusz Repczynski

 

  • JM_nxgn's avatar
    JM_nxgn
    Helper I
    7 years ago

    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's avatar
      Mariusz
      Community Champion
      7 years ago

      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.
      Mariusz Repczynski



  • JM_nxgn's avatar
    JM_nxgn
    Helper I
    7 years ago

    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: 

     

    AnimalIdAnimal
    1Dog
    2Cat

     

    BreedIdBreed
    aChow
    bCorgi
    cGerman Shepherd
    dSiamese
    eRussian Blue

     

    OwnerIdAnimalIdBreedId
    11c
    21a
    32d
    42e
    51a
    62d
    71b

     

    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's avatar
      Mariusz
      Community Champion
      7 years ago

      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.
      Mariusz Repczynski