Forum Discussion

ArjanLoogman's avatar
ArjanLoogman
New Member
8 years ago

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

    • ArjanLoogman's avatar
      ArjanLoogman
      New Member

      Hi Phil_Seamark,

       

      Here it is...

       

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

       

      Kind regards,

       

      ArjanLoogman

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee

        Hi ArjanLoogman,

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



        Best Regards,
        Angelia