4 Replies Latest reply on Oct 2, 2013 11:26 AM by Matt Lutton

# Need a way to use a dimension more than once in an If statement

I have a workbook that requires a manager parameter which would ordinarily be no problem but this one is tricky.  To be able to do this, I had to take each manager's name and manually assign which organization they work for in an If, Then calculation.  Here's a simple example of the formula for reference:

If [Organization] = "Paper"

or [Organization] = "Cardboard"

Then "Jane"

ElseIf [Organization] = "Plastic"

Then "Joe"

ElseIf [Organization] = "Construction Paper"

or [Organization] = "Card Stock"

Then "Fred"

End

This worked great for what I needed until they decided to throw a wrench at me.  I was then told that a new member of the team was added, lets call him Tim, and he wanted visibility into all of these organizations at the same time, while still allowing each individual team member listed above to still see the information relevant to them.  I can no longer do an If, Then statement because after assigning each organization to the team members, you can not then assign the organizations again to Tim. Just to try to explain myself better, I mean cannot do this:

If [Organization] = "Paper"

or [Organization] = "Cardboard"

Then "Jane"

ElseIf [Organization] = "Plastic"

Then "Joe"

ElseIf [Organization] = "Construction Paper"

or [Organization] = "Card Stock"

Then "Fred"

ElseIf [Organization] = "Paper"

or [Organization] = "Cardboard"

or [Organization] = "Plastic"

or [Organization] = "Construction Paper"

or [Organization] = "Card Stock"

Then "Tim"

End

Does anyone have a suggestion on a way to tackle this problem without adding another parameter?

Any suggestions are greatly appreciated.

• ###### 1. Re: Need a way to use a dimension more than once in an If statement

If statements are mutually exclusive, so since you've categorized all of those organization types before "Tim", you'll never find a match for him.

I believe you could write separate calcs for each person to address this:

Jane:

If [Organization] = "Paper"

or [Organization] = "Cardboard"

Then "Jane"

END

Joe:

If [Organization] = "Plastic"

Then "Joe"

END

etc, etc.  This is untested and I'm just thinking out loud, but I think this will allow you to use the duplicate dimension members.

• ###### 2. Re: Need a way to use a dimension more than once in an If statement

Also, you can enforce row level security by using User Filtering as well.  Tableau Server makes this very simple, too, as you can set User Groups, etc.

User Filters and Row Level Security | Tableau Software

1 of 1 people found this helpful
• ###### 3. Re: Need a way to use a dimension more than once in an If statement

Thanks so much for your help Matthew.  I've been staring at this for weeks, I'm not sure why the simple approach of pulling the formula apart didn't hit me.  I haven't attempted User Filtering yet but I think I'll add that to my list of the next things to learn how to do.  It sounds like it would be easier to do it that way, I just have to teach myself how to do it!!  Thanks again!!

• ###### 4. Re: Need a way to use a dimension more than once in an If statement

No problem! If you have Tableau Server, it can be VERY simple to set up User filters--you just select which Server users should see what dimension members, add that as a Filter and you're done.  That method is shown at the link I provided; there are also User functions in Tableau that allow you to filter based on usernames or groups in your database.