3 Replies Latest reply on Jun 9, 2016 7:32 AM by Max Wong

    Count Distinct when data source is from Microsoft Analysis Service

    Max Wong

      Hi guys,

       

      I tried using Count Distinct based on this Tableau article (Getting the Total for Count Distinct | Tableau Software), except that my data comes from a data cube on Microsoft Analysis Service. I have read that cubes do not allow any modification to the data for both dimensions & metrics.

       

      I was wondering, will I be expected to use MDX query to calculate the count/count distinct like the one in the example/article above?

       

      Hope this question makes sense.

       

      Thank you.

       

      Max

        • 1. Re: Count Distinct when data source is from Microsoft Analysis Service
          Dmitry Chirkov

          Looks like someone asked asked similar question without much luck:

          How to perform a distinct count of a cube dimension?

           

          COUNTD() is not supported for cube datasources but perhaps you can try creating something with Calculated Members.

          1 of 1 people found this helpful
          • 2. Re: Count Distinct when data source is from Microsoft Analysis Service
            Joshua Milligan

            Max,

             

            Another option is to use table calculations. It will completely depend on your use-case and desire visualization and it might not be very performant depending on the cardinality of the data, but the basic idea would be to use the Size() function to count how many distinct dimension values occur within a given window.

             

            For example, if I want to count the number of customers, I can place Customer Name (or ID) in the view and then use Size computed along Customer Name to get a count of distinct customers.  It doesn't matter that some customers have more than 1 record.

             

             

            So, I have 793 distinct customers.

             

            Now, when you want to get more complex with the view, you might have to do some crazy things like show only the first or last customer (using First() or Last() as filters), something like this, where I have the distinct number of customers per Category:

             

             

            I moved Customer Name to Detail so I didn't get the Row header and I filtered where First() = 0 (also computed along Customer Name).

             

            Hope that helps spark some ideas!

             

            Best Regards,

            Joshua

            1 of 1 people found this helpful
            • 3. Re: Count Distinct when data source is from Microsoft Analysis Service
              Max Wong

              Thanks for your helps guys!

               

              It is pretty difficult to work with the data from the data cube (Microsoft Analysis Service) where I have to use Calculated Members to manipulate the dimensions. I know this might be off topic but I was wondering, is it possible to group two separate dimensions together to form a hierarchy?