Trying to link two different postcode columns to lookup table

[PowerBiKing]

Hi,

I work in the higher education sector and I'm trying to perform a geographical region analysis for both university applicants and the school they are currently at.

I have an 'Applications' table with a many to one relationship with a postcode lookup table that contains all of the postcodes in the UK, and their respective regions. This relationship is based on the "Applicant postcode" from the "Applications" table. This is working as intended, for example I can count the number of applications and applicants by UK region.

However, I would also like to count the number of schools by region (the "Applications" table also contains "Applicant school" and "Applicant school postcode" columns).

I thought that by creating a new table containing a distinct list of the schools and their postcodes, and creating one to many relationships between this "School" table and both "Applicants" and "Postcode lookup" table, I might be able to acheive what I'm looking for, but I get the following error:

"You can't create a direct active relationship between "School" and "Postcode lookup" because that would introduce ambiguity between tables "Postcode lookup" and "Applications"."

I would appreciate any help with this. I have heard the INTERSECT function may be of use but I'm not sure how to use it in this context.

Thank you.

[HotChilli]

What level of UK 'Region' are you looking for?  I'm sure there will be an easier way than importing the massive UK postcodes table.

[PowerBiKing · Accepted solution]

I've been able to get to a solution that seems to work.

This way I created a custom applications measure using INTERSECT (syntax below). The Applications and Postcode tables are linked on the School postcode in the data model, so I can use this measure in the report that describes applications by region based on Student postcodes.

Applications (Student Postcode) =

CALCULATE(

    SUM([Applications],

    ALL('ApplicationsTable'[Student Post Code]),

    INTERSECT(

        VALUES('ApplicationsTable[Student Post Code]),VALUES('PostCodeLookupTable'[Post Code]))

)

[HotChilli]

Apologies for late reply.

"I would also like to count the number of schools by region" - is this independent of the Applications?

If I'm understanding correctly, you could duplicate the Table P and link that 1:m to table S.  If so, you are now treating Table S as a Fact table for this metric.

Alternatively, you could merge Region field into S from table P (in Power Query) - this would be possible if table S was created in Power Query, I'm not sure if this is where you created it.

--

Let me know if that helps or if I've got it wrong

[PowerBiKing]

Thanks again for replying - no need to apologise!

"I would also like to count the number of schools by region" - is this independent of the Applications?"

- Sorry I maybe should have described this better. Whilst the question above is relevant, I also need to report on number of applications by region (where the region is based on post code of school). Basically I want to have 2 dashboard pages: one focusing on analysis of schools, one focusing on analysis of student. There are subtle but important differences between the two views. Both require region analysis, but the region should be determined by the school postcode and student postcode for each dashboard respectively. Hope that makes sense.

"If I'm understanding correctly, you could duplicate the Table P and link that 1:m to table S.  If so, you are now treating Table S as a Fact table for this metric."

- I thought about this too though I haven't tried it yet. I thought there might be a "cleaner" way to acheive what I'm trying to do without duplicating tables. Also Table P is 1.4GB so I'd prefer not to duplicate it if possible. I'll give this a go though if I can't figure it out otherwise.

"Alternatively, you could merge Region field into S from table P (in Power Query) - this would be possible if table S was created in Power Query, I'm not sure if this is where you created it."

- I created table S with DAX (all of the fields were originally contained in table A). I mentioned the use of INTERSECT in my initial post - could this be used to acheive what your describing?

I'm about to finish work for today so I will try these ideas out next week. Once again I really appreciate your replies and help.

[HotChilli]

Can you post a picture of your model please?

I suspect that you've linked all 3 tables when you want two 1:many relationships.

[PowerBiKing]

Thanks for your reply, please see the screenshot.

A= Applications table

S= School table

P= Postcode table

I'm unable to make an active relationship between S & P.