7 Replies Latest reply on Jun 13, 2017 11:42 AM by Karthik Venkatachalam

    Filtering by User's Domain

    Rachel Furman

      Hello,

       

      I am fairly new to Tableau. I have a dimension called "Office" (sales, marketing, finance, etc.). There is a direct correlation between Office and a user's domain. I need to be able to look at the user's domain and only show data for that office in the worksheet. Certain domains should be able to see data for multiple offices (or all). Is there a way to accomplish this using the USERDOMAIN() function or otherwise?

       

      Any assistance is appreciated!

       

      Thanks,

      Rachel

        • 1. Re: Filtering by User's Domain
          Karthik Venkatachalam

          Hi Rachel,

          You would need to provide mapping between domain and offices somewhere.

           

          One option is to add this mapping in a calculated field

               CALC1:

                    IF userdomain() = 'xxxx' or userdomain() = 'yyyy' then 1

                    ELSE 0

                    END

              

          Now add

               CALC2:

                    IF CALC1 = 1 and (OFFICE = 'Cleveland' or OFFICE = 'Charleston') THEN 'Show'

                    ELSE 'Dont Show'

           

          Drag CALC 2 into filter and show only 1 and you are done.

           

          Another option is to add this mapping as a bridging table in the datasource. Join the field with a bridging table with inner join, which would filter out only the records they are supposed to see. This gives you flexibility in maintaining the mapping in a SQL table, instead of workbook.

           

          Hope this helps.

          • 2. Re: Filtering by User's Domain
            Ankit Goyal

            Yes, Karthik's idea is workable.

            • 3. Re: Filtering by User's Domain
              Rachel Furman

              Karthik,

               

              Thank you very much for your suggestion. I've been working with it and it works well although I'm not certain that it's flexible enough for my specific situation. Each time a user goes to the view, he/she should see the data for the office associated with his/her domain. It needs to be dynamic in that way. My apologies- I realize I didn't explain it well enough in my original post.

               

              I've tried the following although I think it needs some tweaking.

               

              Calc1

              IF USERDOMAIN() = 'xxxx' THEN 'Cleveland'

              ELSEIF USERDOMAIN() = 'yyyy' THEN 'New York'

              ELSEIF USERDOMAIN() = 'zzzz' THEN 'Chicago'

              END

               

              Calc2

              IF [Office] = [Calc1] THEN [Calc1]

              ELSE '?' --not sure here

              END

               

              I then dragged Calc2 into Columns and selected all. I'm not clear what the ELSE statement should be in Calc2 or if this is the best approach. Any suggestions would be great.

               

              Thanks,

              Rachel

              • 4. Re: Filtering by User's Domain
                maneesh.gaddam

                Can you create calculated field as [Office]=[Calc1] and throw this field into filter and select True.

                • 5. Re: Filtering by User's Domain
                  Karthik Venkatachalam

                  The way you have written is perfect. "ELSE" is not needed. Because, we are not interested in the unmatched records. This way when user logs in, the cities they have access to, would be shown. If not, no records would show.

                   

                  For e.g if a user belongs to a domain 'aaaa' then Calc 1 would be NULL. And for Calc 2, if there are NULL values in the data for the office column, it would be displayed. Else, No records would show.

                       - Talking about null values, make sure "Office" field is clean, and if you come across Nulls, you have to handle them in the logic above.

                   

                  This is definitely a good approach. As long as you document the logic well for other future folks to maintain this security model, you should be fine.

                  • 6. Re: Filtering by User's Domain
                    Rachel Furman

                    That makes sense regarding the null values. I streamlined this a little and added the ability to show all offices when needed (THEN [Office]). It seems to work really well.

                     

                    Calc1

                    IF USERDOMAIN() = 'xxxx' THEN 'Cleveland'

                    ELSEIF USERDOMAIN() = 'yyyy' THEN [Office]

                    ELSEIF USERDOMAIN() = 'zzzz' THEN 'Chicago'

                    END

                     

                    Calc2

                    [Office] = [Calc1]

                     

                    I dragged Calc2 into the filter and selected True as Maneesh suggested.

                     

                    Thanks again. This was extremely helpful!

                    • 7. Re: Filtering by User's Domain
                      Karthik Venkatachalam

                      Glad you were able to get it working.

                       

                      Please remember to close out the thread by marking it answered.