Sneak: Distinct Count

The Tips&Tricks Series is produced by the Customer Success Team. It highlights several easy to follow instructions and range from simple tasks to more advanced use cases.

Adding a “Distinct Count” measure against a “Dimension” key in “Model”

In this scenario we will provide a method to create a “Distinct Count” measure within a “Model” based upon a column used for a dimensional relationship. This method allows for the measure to be created with a “business friendly” naming convention without affecting the column name used by the key relationship within the model.

In this example we will create a “Customer Count” measure. Our model contains a number of tables, the central “Fact” tables named “SalesData” contains our sales based metric data, in addition it has “key” fields that are used for the dimensional breakdown of the metrics.

1. One of these is called “CustomerKey” that is used to link to the “Customer” dimension table

   

2. Our requirement is to have a count of customers based upon the “SalesData” fact table.
   The most natural (and simplest) way to do this would be to go into “Columns” in the “Model” definition and select “Distinct Count”in the measure column against the “CustomerKey” column. This would create the measure required BUT it would have the business unfriendly name of “CustomerKey”, it is possible to rename the column to the measure name BUT this would then make the model less intuitive as the Key column names would no longer match.

   

3. To address this we can use the “Multiple Measures” feature of the “Model” tool.
   Against the column we wish to create a distinct count for we go into the “Measure” drop down and select “Add Multiple Measures”

   

4. In the “Multiple Measures” dialog, within “Display Name” type your business friendly measure name (in this case “Customer Count”) select “Distinct Count” in the “Measure” drop down and then click on the minimize button (do NOT click “Add Measure” as this is used for adding an additional measure not saving the measure you have just added... :) )

   

5. This has now added the measure with the friendly name without changing the column name in the data model

   

If you have questions about these tips (or improvements) please let us know in a comment.

 

P.S. We will post previous Tip&Tricks as well as all new ones to keep this information active and available to all users.