0

I have a table that has 1K rows. In the table there is a column that has the names of the customers. I need to add a column that counts (index) how many customers I have.

Doing a calculated measure using the distinctcount formula I get 3156 customers. My goal is to accomplish the same result of the calculated field in a calculated column.

Thanks for the help.

Chapin
  • 31
  • 1
  • 2

1 Answers1

0

Not sure about a calculated column but one way you can accomplish this is to make related dCustomers dimension table from your source. You question didn't happen to mention your source - here is how I'd do it for the most common sources in my estimation:

SQL database connection:

SELECT DISTINCT customerField
FROM yourViewOrTable

Excel/Text File:

  1. Duplicate the worksheet with the Linked Table.
  2. On the worksheet copy delete all columns except the Customers column.
  3. With the a single cell active in the data go to Data>Remove Duplicates.
  4. Under the PowerPivot ribbon tab click Create Linked Table.

Now What? You should now have two tables from whatever source you are using. You'll find your new table has 3156 records in it. Go to Diagram view and drag a relationship from table1.CustomerField to table2.CustomerField.

With the relationship made you should be able to do anything you need to do, but please fire back if you have any questions on your use case.