Forum Discussion
Relationships and calculated columns
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
4 Replies
- Phil_Seamark
Microsoft Employee
- ArjanLoogmanNew Member
Hi Phil_Seamark,
Here it is...
Had to translate the project into English, took a while.
Kind regards,
ArjanLoogman
- v-huizhn-msft
Microsoft Employee
Hi ArjanLoogman,
Please change the single filter to both cross filter direction as follows. Then check if it works fine.
Best Regards,
Angelia