Relationships and calculated columns

[ArjanLoogman]

First of all: Hello! Newbie here!

Nice to have found a place to learn...

I might have been trying this all the wrong way, but here goes:

A have four different tables, each containing info such as type, address and zipcode of a specific kind of 'institution' whithin one city (i,e, dentists, pharmacy).

I have another table containing a column of zipcodes and a column of city regions. Multiple zipcodes apply to one city region.

Managing relationships and using the RELATED function I have been able to add a column to each institution's table showing to wich city region each dentist or pharmacy, etc. belongs.

Now, I would like to calculate how many of each institution there are per city region and put those numbers in a seperate table (NewTable) with the names of the city regions. Also, I would like to use that number in further calculations (i.e. divide the population per city region by the number of pharmacies in that city region).

I've tried things like CALCULATE(COUNTROWS(Dentists);FILTER(Dentists;[CityRegion]=NewTable[CityRegion])), but that gave me all blank fields.

Can anyone help me? I've been staring at this for a week now and I feel this should be easy. Have I overlooked anything?

Greetings, ArjanLoogman

[Phil_Seamark]

Hi @ArjanLoogman

Any chance you can post a screenshot of your relationship view for these tables?

[ArjanLoogman]

Hi @Phil_Seamark,

Here it is...

Had to translate the project into English, took a while.

Kind regards,

ArjanLoogman

[v-huizhn-msft]

Hi @ArjanLoogman,

Please change the single filter to both cross filter direction as follows. Then check if it works fine.



Best Regards,
Angelia

[ArjanLoogman]

Hi Angelia,

Thank you for your tip! Unfortunately, this didn't solve the situation.

Riddle me this:

I've succeeded doing a similar count with two simple tables: one table  with a column for teacher names and another table with a column for class number (1-10) and a column for attending teacher.

using both: numClasses = COUNTROWS(FILTER(Classes;Classes[Teacher]=Teachers[Teachers]))

and: numClasses = COUNTAX(FILTER(Classes;Classes[Teacher]=Teachers[Teachers]);[Teachers])

I've managed to calculate how many classes each teacher has given.

Unfortunately, these formulas don't work on my other project.

The only difference between Classes[Teacher] and the column from my problematic project, Dentists[CityRegion], is the fact that the latter is a calculated column based on a lookup from a related table: CityRegion=Related(CityRegionsAndZipCodes[CityRegion])

Classes[Teacher] is simply a manually filled column.

Thoughts, anyone?

Kind regards,

ArjanLoogman