5 Replies Latest reply on Aug 19, 2016 7:55 PM by Tom W

    Logged in UserName as Filter criteria across multiple columns

    Data Junkie

      Hello,

       

      I have been struggling with creating the most efficient filter/calculation combination which would use the UserName() result to locate which column of data the value exists in and then filter the viz based on the located column.

       

      For Example:

       

      I have a data set of sales transactions for my company. I have 7 employees across 3 levels of authority. When any of the 7 employees log in, I would like them to see only their data and the data of their direct reports. (Jesse can only see Jason & Tom)

       

      In my data, each transaction includes the UserName() of each level of authority above the user. (Tom's transaction includes the UserName() of Jesse as Store Manager & Roger as District Manager)

       

       

       

       

       

       

       

      This is my base data set.

       

       

       

       

       

       

       

       

       

      When James logs in to view the dashboard, he should only see this data.

       

       

       

       

      When Sally logs in to view the dashboard, she should only see this data.

       

       

       

       

       

       

       

       

      When Roger logs in to view the dashboard, he should only see this data.

       

       

       

       

       

       

       

       

      When someone other than my 7 employees log in, they should only see this data.

       

       

       

      So far, I can easily filter the data for any of the authority levels, but only one at a time.

       

      CASE [Clerk ID]

      WHEN  UserName() THEN 1

      ELSE 0

      END

       

      I'd like help in creating (Ideally, one ) the calculation and filter which identifies the authority level which the logged in user exists in and then filters the data down to only that authority level and that employee (or employee's team). Id really appreciate any help which you can offer. Sorry for all the pictures... I find it helps explain my challenge better than my words.

       

      Oh yea, I almost forgot to add... UserName() is the only User variable which I can use. All other variables and permission groups / settings are not an option in my environment. I'm trying to encapsulate the security all within the workbook. (for better or for worse)

        • 1. Re: Logged in UserName as Filter criteria across multiple columns
          Tom W

          Hi,

          I would create a calculated field called FilterInclude as follows;

          Username() = [Store Manager ID] or Username() = [Supervisor ID] or Username = [Clerk ID]

           

          That field will return true or false - drag it onto the filter shelf and set it to true.

          1 of 1 people found this helpful
          • 2. Re: Logged in UserName as Filter criteria across multiple columns
            Luis Vicentini

            Just gave this a try with a quick CSV copy/paste and a Parameter instead of the actual UserName(), and it worked perfectly. Props, Tom W.

            I find getting this to work from a single line with just direct true/false checks really clever. If I'm thinking through this correctly, that calculated field will check through all the values in [Store Manager ID] as a first step, and if it finds a match return True and move on.  If it doesn't find a match, it'll set to a "temporary" False and move on to check through all of [Supervisor ID], and the same thing happens - if there's a match, True and stop, or continue with the "temporary" False as it runs through [Clerk ID].

            Is this why it's filtering properly through the Hierarchy levels? It makes the order of the OR statements very significant if so. 

            • 3. Re: Logged in UserName as Filter criteria across multiple columns
              Tom W

              Luis,

              It will evaluate the criteria in the method you've suggested. The order isn't really important though, it's not actually enforcing a hierarchy through the order of the conditions. If you reversed the order of the conditions it would return exactly the same result.

              It's filtering 'properly' through the hierarchy levels because the data is structured in such a way that I can use the OR to run the test on multiple fields and if any of them are true I know I'm good to include that data for the results. It's partially driven by the fact this is a really simple hierarchy. The same approach wouldn't necessarily work on a more complex hierarchy.

              • 4. Re: Logged in UserName as Filter criteria across multiple columns
                Data Junkie

                Wow, Tom W... I've spent far too many hours than I'd like to admit today over engineering multiple cascading calculations and filters to achieve the outcome that your recommendation achieves. My thanks for your response... you've saved me hours of over thinking this weekend.

                • 5. Re: Logged in UserName as Filter criteria across multiple columns
                  Tom W

                  Sometimes the simplest solutions are the best! I'm glad I could help out.