1 2 Previous Next 15 Replies Latest reply on Mar 7, 2018 3:50 PM by Shinichiro Murakami

# Help needed!!! - A dynamic dimension based on Count Distinct

Hi everyone,

In my head this one was straight forward, however I have hit a brick wall pretty early on.

I'm hoping that someone here will be smarter than me and able to suggest a solution.

I have data at different levels in a hierarchy. These are like Departments, Business Units, Cost Centres:

I want to create a calculated field called Dir x which can be Dir 2, Dir 3 or Dir 4 depending on the filters applied.

in terms of simple logic, my original idea was something like:

If           Total (countD ( [ Dir 2 ] ) >1 Then  [Dir 2]

Elseif    Total (countD ( [ Dir 3 ] ) >1 Then  [Dir 3]

Else       [Dir 4]

End

With this, if a Viz only had 1 bar at Dir 3 level, it would drill down automatically to Dir 4 - amazing, right?

My issue is that I can't combine Aggregations  with "If" functions.

I feel like the solution is in LoD calcs but I haven't been able to find one.

If anyone can suggest a work around it would reduce my number of sheets by 66% so there is a lot at stake for here !!!!!

As always - I look forward to your ideas.

Many thanks,

Ed.

• ###### 1. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Ed,

What kind if error are you getting? Does it say "Cant mix aggregate with non aggregate functions ..." or something like this? Also, is it possible to post your workbook or mockup of your data so we can tell how to fix it please. Thanks

• ###### 2. Re: Help needed!!! - A dynamic dimension based on Count Distinct

This does not necessarily mean I'm smarter than you, but for sure, I love Tableau more than you.

You can achieve this with below formula.

if {fixed [Dir-1]: sum([Number of Records])}=1 then [Dir-1]

elseif {fixed [Dir-2] :sum([Number of Records])} =1 then [Dir-2]

else [Dir-3] end

Thanks,

Shin

• ###### 3. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Hi Shin,

thanks for sending this through. I think we are very close to a solution, however -

Is there a way to make it so that only Dir 2 OR Dir 3 show at any one time? At the moment we have a mixture of both levels.

Kind Regards,

Ed.

• ###### 4. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Hi Sherzodbek,

Shin's file works pretty well as a mock file if that helps.

"Cant mix aggregate with non aggregate functions ..." - that's the error I get, because its not possible to mix "if" functions with "countd"

The reason I want this is so that users who have different access rights can view the same report with it all ready "drilled" to their required level.

Many thanks,

Ed.

• ###### 5. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Not sure I understand your request, but is this what yu said?

if {fixed [Dir-2] :sum([Number of Records])} =1 then [Dir-2]

else [Dir-3] end

Thanks,

Shin

• ###### 6. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Shin, he wants an automatic drill-down.

It's something like this:

If Countd([Dir-1])     = 1 then

If Countd([Dir-2]) = 1 then [Dir-3]

Else [Dir-2]

End

Else

[Dir-2]

End

I was trying to adapt using fixed but it didn't work.

• ###### 7. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Still not 100% sure, but is this the requirement?

Could you tell me What do you mean by "drill down" if this still does not meet the expectation.

[Calculation1]

if {fixed [Dir-1]: sum([Number of Records])}=1 then [Dir-2]

elseif {fixed [Dir-2] :sum([Number of Records])} =1 then [Dir-2]

else[Dir-3] end

Thnaks,

Shin

• ###### 8. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Hi everyone,

Thanks for your time on this one.

I think Luciano has understood what I am trying to do, but it is probably only possible by incorporating FIXED.

Shin - let me explain here:

Imagine a supermarket business. In the Supermarket there is an overall manager, two assistant managers, and some category analysts for each product. They all want to look at a cost report, but they need to see it at different levels of granularity.

When the overall manager looks at the report all charts and graphs should show Food and Non Food. (Dir 1)

When the Assistant manager views the report Graphs should show the Dir 2 categories for their area (Food or Non Food). This means the Food manager should see Bakery, Fish, Meat, and Sweets.

The category analysts are responsible for 1 Dir 2 level category each. This means when they login they should see all graphs at their Dir 3 level of detail. For instance a Category analyst for Books should see charts split by Fiction and non Fiction.

all of this will be driven by filters driven by their login and password.

So to summarise. If the total(countd) of a level is 1, I want to show the next lower level.

If there is only 1 distinct Dir 1 (e.g. Food) then show  Dir 2 (Meat, Fish, Bakery, Sweets).

If there is only 1 deistinct Dir 2 (e.g. Fish), then show Dir 3 (Fresh, Frozen).

I hope this helps.

Many thanks,

Ed.

• ###### 9. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Still not sure I understood the request, let me know if this still does not help.

if

{fixed:countd([Dir 1])}=1 and {fixed:countd([Dir 2])}<>1

then [Dir 2]

elseif

{fixed:countd([Dir 2])}=1

then [Dir 3]

else [Dir 1]

end

Change all he filter to "Context"

Thanks,

Shin

• ###### 10. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Shin,

I can not thank you enough for this!!

I owe you a coffee.

I am really excited by this - I think what we have here is something that can be applied to a lot of situations.

Have a great weekend.

Ed.

• ###### 11. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Happy to hear that, but please remember,

LOD sometimes causes the slow response because of it's feature.

If you have tons of data set, it's not always attractive from performance  standpoint.

As long as you use extract, I think that's still OK.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 12. Re: Help needed!!! - A dynamic dimension based on Count Distinct

Shin - Thank you very much for this!  I've been looking to solve this for some time.

• ###### 13. Re: Help needed!!! - A dynamic dimension based on Count Distinct

HI Alisa, Alisa Dubreuil

This is one of very tricky approach which I wanted to remember , but I could not find this post easily by myself recently.

I'm just curious how you reached here?

I am asking search word or path which brought you here.

Regards,

Shin,

• ###### 14. Re: Help needed!!! - A dynamic dimension based on Count Distinct

My Google Search: TABLEAU DYNAMIC DIMENSION FROM LOD   Again, this is smart stuff.  Thank you.

1 of 1 people found this helpful
1 2 Previous Next