[TIP 104] - Return the Count of Unique Members in a Hierarchy

#1

Most people don’t know this, but you can ask Dundas to give you the count of unique members in a hierarchy by dragging a hierarchy into the measure section of the data analysis panel.

This will return a COUNT DISTINCT (for those of you used to the database expression), which returns the number of unique values in the column you select.

Basically – I have a data set on the right and I can get data for the number of values in any of my hierarchies by simply dragging it into measure.

  • Drag Continent --> we have sold to 6 Continents
  • Drag Country --> we have sold to 84 Countries
  • Drag City --> we have sold to 160 Cities
  • Etc.

Check out this video of this in action:

logo

#2

What if I also want to filter the Metric Set for the same Hierarchy by adding it as a Slicer? This would show the Count of Unique Members based on if a filter was applied to the Hierarchy.

It appears this isn’t possible in Dundas BI. If you try to add the same hierarchy to the Slicers section of your metric set, it gets moved from Measures to Slicers (or vice versa). It makes sense to me to remove it from the Rows, but not the Slicers.

To work around this, you can bring in the same field twice, or use the copy transform if you are using data cubes, however this also requires bringing over duplicate data.

1 Like
#3

@tom.hagan if it is a Measure you can filter on it and it is displayed. if it is just a Slicer you can filter on it but it is not displayed.

no need for a work around on this.