Forum Discussion
Trying to link two different postcode columns to lookup table
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])))
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
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.