View Full Version : DAX Measure: Count number of cities in each state

2015-05-11, 08:02 PM
I have a table with Cities and States. I need a measure that counts how many distinct cities exist for each state.

As an example, say there are 500 cities in CA and 10 cities in NV. My measure should return:

Los Angeles 500
San Diego 500
Las Vegas 10
Reno 10

Obviously if I wanted the results for each state I could do a simple distinctcount and be done with it. However, since my results are for each city, that's where I'm having some problems.

Thanks in advance for any help!

2015-05-12, 01:08 AM
Excel 2010 with free PowerPivot and Power Query Add-In.
Compatible with Office 2013 Pro Plus.
With Related()