4 Replies Latest reply on Jun 8, 2016 9:37 AM by Joshua Milligan

# How to perform a distinct count of a cube dimension?

Hi,

I’m relatively new to Tableau and have a question about creating calculations in Tableau when using cubes, as I know from an article on the website that some of the features are different/not there with cubes. My cube is a Microsoft Powerpivot one hosted on Sharepoint, if it makes  a difference.

Imagine a table of sales, 1 row per sale, with fields like customer ID, date and # products purchased.

I would like to make a calculation in Tableau of average products sold per customer, with a view to making a chart showing change in products sold per customer. The calculation I therefore need is:

Sum of product purchased / count distinct of customer IDs
over a period.

I know the sum and countd function but can’t understand how to make it work in terms of the cube data in Tableau.

1) If I go to “create calculated field” then in my “fields” list I have only the measures listed, and not the dimensions. Customer ID is a dimension so I can’t do a countd of that in my calculated field it seems.

2) If I right click customer ID on my dimensions list I can choose create measure, and it generates me a “Count of customer ID” measure, but this is a count and not a distinct count. I can right-click -> edit that new measure to change the aggregation to sum, average, minimum, maximum but there is no distinct count as an option.

3) Because it is cube data I cannot create a local Tableau data extract and play with it there.

I guess this is a common requirement so I apologise if I miss something obvious. Any hints appreciated!

that for “advanced calculations” I can use MDX. I imagine the above isn’t really "advanced", and in any case I don’t know how to reference fields in MDX…it looks quite complicated for mere business users to learn in a hurry :-)

Thanks for any help!

• ###### 1. Re: How to perform a distinct count of a cube dimension?

Cubes are difficult to work with in this sense. It's been awhile since I've used Tableau with one, but I believe you'd have to create the distinct in your data source, rather than in Tableau.

1 of 1 people found this helpful
• ###### 2. Re: How to perform a distinct count of a cube dimension?

The best way to handle this is to *** your cube developers to implement a count distinct on customers in the cube. Usually it performs very well and it is not that hard to implement.

Hope it help

Cheers,

Peter

1 of 1 people found this helpful
• ###### 3. Re: How to perform a distinct count of a cube dimension?

Thanks Kyle and Peter, that is the approach I took thanks to your advice as I couldn't find an easy Tableau-only way. As mentioned it performs well (just requires the co-operation of IS departments to set up :-) ).

• ###### 4. Re: How to perform a distinct count of a cube dimension?

The thing to understand about cubes is that the balance of analytical functionality is taken by the cube.  That's by design and part of what makes them so powerful (but also "limited" in what you can do beyond how the cube was built).  So, yes, if possible, get your cube designers to modify the cube and hopefully they do it fast (though not as fast as you'd do it in Tableau with a relational data source!) and you'll be good to go.

Still, Tableau offers some incredible possibilities for working around the fact that cubes try to do all the work.  See my answer to the similar question here for a way to count distinct values of dimensions in a cube: Count Distinct when data source is from Microsoft Analysis Service

Best Regards,

Joshua