2

I'm using SSAS and created a measure group that is linked to Fact_SalesMetrics on SQL Server. In that, I have seven measures. All measures have aggregation of Sum except Sales Invoice which has aggregation of Distinct Count.

Everything works great. I need to make the aggregation of 'CustomerID' also to Distinct count, but I can't be able to. It says me error states, "The measure group has more than one distinct count measure."

So, I need to have these two measures's aggregation as DistintCount. How can I? Any help will be appreciated.

I thought to create a calculated member to achieve this but got failed as I don't know what expression I have to give!

  • Are there multiple CustomerID values for each invoice? If not, then a COUNT on CustomerID should work fine. – LowlyDBA Apr 3 '15 at 13:19
  • If not multiple values, count will exactly work. But there'll be a lot of repeating values. That's why I need a distinct count! – good-to-know Apr 3 '15 at 13:24
2

The limitation of a single distinct count per measure group is related to performance. To get both distinct count measures to show up in the same measure group, you can use the workaround suggested here by Darren Gosbell.

best of both worlds...performance benefit from each DC-measure residing in its own measure group, UX benefit from having all related measures appearing in same measure group.

2

This helped me in getting it working!

Just go to your cube and right click. Choose “Distinct count” from the begining.

enter image description here

When you click ok. The new Measure Group will be created automatically. And you wont see any warning messages.

enter image description here

I hope this post helps anyone who was stuck on creating Distinct Count Measure like me.

Complete definition is here:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.