9 Replies Latest reply on Sep 15, 2016 10:28 AM by David Li

# Remove duplicated dimension

I have following situation:

If I count distinct number of ID numbers, then I get 5. However, if I count distinct IDs by Program, then I get 6.

So, any idea how i can get 5 if I want to get ID numbers by program? Thank you

• ###### 1. Re: Remove duplicated dimension

Hi,

When you build a view, Tableau "aggregates" the values depending on the dimensions you're using. In your example you have 5 different ID thus you get 5 as a result of a count distinct. However when you count distinct your program you notice there is an extra row for the Id=3, thus you get 6 as a result.
Try the LOD calculation;

{EXCLUDE program: countd(ID)}

More about LOD calculations

Hope this helps,

Regards,

• ###### 2. Re: Remove duplicated dimension

Hi Sherzodbek! The reason you get this is because of ID 3, which appears in two programs. So if you look at each program individually, ID 3 is properly counted as a distinct ID number.

What exactly would you like to display? For instance, would you expect B to show "1" here, so that an ID is only counted in the first Program (sorted alphabetically) it appears in?

• ###### 3. Re: Remove duplicated dimension

Hi Stephane,

I tried {EXCLUDE program: countd(ID)}, yes it does give me 5 IDs I want, but this not a look I am looking for. To be more specific, I want to get distinct IDs for each program (like A-2, B-1, C-2 so total 5) therefore count only first program enrolled and ignore next program if student was enrolled more than 1 program.

• ###### 4. Re: Remove duplicated dimension

Hi David.

Yes, I wanted to B show a count of 1. Is it something to do with portioning and addressing then?

• ###### 5. Re: Remove duplicated dimension

No, it's different. You aren't using table calculations, so partitions and addresses aren't relevant. But I think I might have a solution. Try using this LOD calculation:

{ FIXED [ID] : MIN([Program]) }

Put that on the rows shelf instead of [Program] and see if it works.

1 of 1 people found this helpful
• ###### 6. Re: Remove duplicated dimension

Hi David,

Yes, I got the result I wanted as you advised. It looks like this now, same look I was looking for. Thank you for your time and effort. Also, I have more than 1 dimension where student will change overtime, but I have to count only once for that dimension ( i believe there are about 5 dimension that changes over time). Do I use the same formula for each one or do I combine all of them in one calculated field with FIXED? For example of my dimensions: Military Affiliation (from Active Duty to Veteran or Reserved), Majors and so on. Any further help on that please?

• ###### 7. Re: Remove duplicated dimension

I'm glad that worked!

Regarding the other dimensions, I'm pretty sure that you'll have to create a version of this LOD calc for each of them unless you would considering collapsing all of those dimensions into a single one (so that they would display as a single column, not separate ones).

1 of 1 people found this helpful
• ###### 8. Re: Remove duplicated dimension

David,

You rock. Yes, I was able to combine two dimension as a single dimension and apply LOD expression and it worked like a charm. Thanks again for a help. You saved me a day.

• ###### 9. Re: Remove duplicated dimension

Hurray! I'm very happy I could help!