3 Replies Latest reply on Jul 20, 2018 12:54 AM by Santiago Sanchez

    Multiple IF Statements in Field as User Filters

    Geraud de Larquier

      Hello,

       

      I am evaluating the way we are filtering our reports for our Client Users today. The majority of our Tableau Server users are Clients which all have access to the same reports, filtered to their own data. To securely filter the data, I initially setup 3 main User Filters which were added as Sets, and that I have to modify for every new client user or change request. The 3 fields filter different aspect of the data (Client / Country / etc).

       

      After going through the following article I reviewed a different way to filter the data per user which I tested and turned out to be more efficient & improve the performance of the reports (https://tableauandbehold.com/2016/08/08/defusing-row-level-security-in-tableau-data-extracts-before-they-blow-up-part-1/). Basically maintain 1 calculated field where all user filtering is handled - it looks like this:

       

      IF ISMEMBEROF('ClientA') THEN IF [Client Name] = 'ClientA' THEN TRUE ELSE FALSE END

      ELSEIF ISMEMBEROF('ClientB_CountryA') THEN IF [Client Name] = 'ClientB' AND [Country] = 'CountryA' THEN TRUE ELSE FALSE END

      ....

       

      My main question is for someone that went through this process, is it a challenge to maintain this type of field or does it have the potential to become a slug as we get more and more new distinct users? In other words, is it a problem for Tableau to have many IF statements of that type in one field. Thanks in advance for any help,

       

      Geraud

        • 1. Re: Multiple IF Statements in Field as User Filters
          Santiago Sanchez

          Hi Geraud,

           

          Just to let you know, you are looking at the right resource in terms of best-practices for handling row level security in Tableau. Bryant's blog does a fantastic job of collecting proofed solutions, and he keeps them very up-to-date. That said, as the number of IF conditions gets long, it'll become more challenging to maintain and will have an impact in performance (how big depends on many factors, including your the size of your data set, but could as well be unnoticeable). If you don't think the list of IFs will get very long, and you are satisfied on the performance once you've setup the filter and tested it, I'd say you are good to go.

           

          As that blog post ends, there are other solutions worth checking on part 2. The one I've seen most commonly used is CONTAINS, this gives you the flexibility to consolidate several possibilities in a single condition. Worth testing if you are concerned about the number of IFs. 

           

          Both blog posts are useful when you are working with extracts. If you are working with live connections, I'd also suggest reading the parent blog post.

           

          Hope this helps!

          Santiago

          1 of 1 people found this helpful
          • 2. Re: Multiple IF Statements in Field as User Filters
            Geraud de Larquier

            Thanks Santiago for your assistance! I was about to review the Part 2 but got discouraged when they mentioned the Contains calc would be challenging in the 10.5 version. (We're still in transition to move to 2018.1.1.). I'll take another look.

             

            I know it's very dependent on the type of data set we have, but do you think there is any "rule of thumb" numbers that indicate when the performance gets hit by such calculation? e.g. "starting from 200 IFs conditions & looking through a 10 million rows data set, the performance starts to take a noticeable hit.". It's simplistic I know but I'm trying to get the best opinions before moving ahead.

             

            Thanks again,

             

            Geraud

            • 3. Re: Multiple IF Statements in Field as User Filters
              Santiago Sanchez

              No worries! I'm not aware of a 'rule of thumb' on this respect. If there are not that many conditions to type, I'd encourage to test it out. Other than that, I'd wait to hear from other members of the community too!