# Several Distinct Count measures in the same fact table?

• ### Question

• Hi,

I am currently trying to use several distinct count measures based on the same fact table... but i have two questions in my head.

Can i created a distinct count measure based on several columns?

Can i created several distinct count measures on the same measure group? Or will i have to create a measure group for each distinct count? This will bring User Friendly to the ground since i will be creating 3 or 4 new groups with only one measure each... can i group them somehow?

Best Regards,

Luis Simões

Tuesday, December 18, 2007 11:41 AM

• Hi,

1).  To create a distinct count measure based on several columns, you need to create a artificial column in the fact to represent the unique combination of the columns.

2).  To my knowledge, one distinct count for one measure group for performance reason, not sure this is a physical limitation.

3).  To overcome too many measure groups, hide all the distinct count measures, define calculated member for each distinct count, assign the calculated members to a specific measure group you want.

HTH.

Tuesday, December 18, 2007 8:02 PM

### All replies

•

Hi,

>Can i created a distinct count measure based on several columns?
No, you can't. Only on one column.

>Can i created several distinct count measures on the same measure group?

Do you mean the same fact table?

For every DC measure a separate measure group will be created

>can i group them somehow?

Yes. You should set appropriated DisplayFolder for these measures.

Tuesday, December 18, 2007 11:59 AM
•

Hi,

>>Can i created a distinct count measure based on several columns?
>No, you can't. Only on one column.

Any trick for achieving this?

>>Can i created several distinct count measures on the same measure group?

>Do you mean the same fact table?

Yes always same fact table.

>For every DC measure a separate measure group will be created

>>can i group them somehow?

>Yes. You should set appropriated DisplayFolder for these measures.

But displayFolder is created for each measure group so i needed to avoid that many measures groups at least for the user to choose... ideas?

Regards

Tuesday, December 18, 2007 12:42 PM
• Hi,

1).  To create a distinct count measure based on several columns, you need to create a artificial column in the fact to represent the unique combination of the columns.

2).  To my knowledge, one distinct count for one measure group for performance reason, not sure this is a physical limitation.

3).  To overcome too many measure groups, hide all the distinct count measures, define calculated member for each distinct count, assign the calculated members to a specific measure group you want.

HTH.

Tuesday, December 18, 2007 8:02 PM
•

To my knowledge, one distinct count for one measure group for performance reason, not sure this is a physical limitation.

-- Whats the logic behind the above performance tip?

-- Can I have a measure which uses Min and a measure which uses Distinct Count in the same measure group? Will the above performance tip apply only when there are multiple distinct counts or for multiple aggregate functions?

Thursday, April 17, 2008 4:01 PM
• Hi,

I think that AS handles distinct count dramatically differently from other provided aggregation in terms of structure and aggregation, thus it will be more efficient to handle special structure in a seperate measure partition than mixing with other measures.

Yes, it should be fine to have min and distinct count (but for performance reason, it may be better to separate them), but I don't think it is possible to define multiple distinct counts in a same measure group (by design due to the potential ineffectiveness).

Friday, April 18, 2008 2:45 PM
• Yes, it's possible to create some measures with the DISTINCT COUNT AgregateFunction property in the same measure group. In the datasourceview from the cube you must select the column you want to make the measure from and right click the mouse. Select [New Measure from Column]. It is created with the SUM AgregateFunction property. You must just change the AgregateFunction property of the measure and the name you want.

Tuesday, November 18, 2008 5:14 PM
• lol, good hack.
Friday, February 6, 2009 5:00 PM
• Seems it does not work on 2008R2 - there is error during build
Error 18 Errors in the metadata manager. The 'NNNN' measure group has more than one distinct count measure.  0 0
Wednesday, February 8, 2012 8:36 AM