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

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

    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.