Forum Discussion

PowerBiKing's avatar
PowerBiKing
Frequent Visitor
3 years ago
Solved

Trying to link two different postcode columns to lookup table

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...
Need Help
  • PowerBiKing's avatar
    PowerBiKing
    3 years ago

    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's avatar
HotChilli
Community Champion
3 years ago

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's avatar
PowerBiKing
Frequent Visitor
3 years ago

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]))
)