Count number of cities in state

[rdlib]

I have a table that lists States and Cities. 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 result should look like

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.

I tried the following with no success
StateID:=values(Table1[State])
Measure2 = CALCULATE(DISTINCTCOUNT(Table1[City]),filter(all(table1),Table1[State]=[StateID]))


If you can somehow get this working the second part is to add additional filters, so do a count of the cities but only those cities with populations over x.


Any help is greatly appreciated. Thanks!

 

[Kazlik]

Give this a try and am sure there is an easier way but worked on a test file for me.

CityCount:=CALCULATE(COUNTROWS(DISTINCT(Table1[City])),FILTER(ALL(Table1[City]),Table1[City]=Table1[City]))

StateCityCount:=SUMX(DISTINCT(Table1[State]),[CityCount])

 

[rdlib]

Wow thank you Kazlik. Seems strange that such a simple measure requires such a complex solution but it worked perfectly so that's fantastic!

Can you explain what "Table1[City]=Table1[City]" is doing? It works but don't understand the logic behind it. Thanks

 

[rdlib]

Kazlik, I wanted to include a filter for population and it works

=CALCULATE(sumx(DISTINCT(Table1[State]),[CityCount]),Table1[Population]>100)

The table headings for table1 are State, City, Population, Date. I have data going back a year so each city has multiple entries. I have multiple other tables and they all link back to a parent table called 'StateCityUnique'.

When I use my City column from the 'StateCityUnique' table in my PivotTable my StateCityCount measure no longer works. Any ideas what to do?

 

[Kazlik]

If you have a relationship say on city between StateCityUnique and this table then you should be able to update the measure and then pull in the sate, city from StateCityUnique and then your counts would be based of the data in table1. I am still new to this like you so they could be a simpler way to do this as well.


CALCULATE(COUNTROWS(DISTINCT(StateCityUnique[City])),FILTER(ALL(StateCityUnique[City]),StateCityUnique[City]=StateCityUnique[City]))

 

[rdlib]

Even though my list of cities come from StateCityUnique table, I need to do the count off of Table1 because I need to filter based on month and population (the StateCity Table doesn't have this data). In any case i greatly appreciate all the help you've been so far. I feel like i'm a lot closer to a solution.