Data model and measures

With the tables loaded, connect them and add a few measures. Everything here happens in Power BI Desktop, outside the Power Query Editor.

Relationships

Open the Model view. Power BI may detect some relationships itself. Make sure you have exactly these, all one-to-many with a single cross-filter direction:

From (one)	To (many)
Portfolios[id]	Properties[portfolio_id]
Properties[id]	Hazards[property_id]
Properties[id]	Biodiversity[property_id]
Properties[id]	Buildings[property_id]
Properties[id]	EnergyCertificates[property_id]

EnergyCertificates[building_id] points to Buildings[id] too, but don't add that relationship. It would create a second path from Properties to EnergyCertificates, which Power BI can't resolve.

Filter properties by their hazards

With single-direction relationships, a slicer on Properties filters Hazards, but not the other way round. If you want a slicer like "Hazard = landslide, Exposure = HIGH" to filter a table of properties, either:

• set the Properties → Hazards relationship's Cross filter direction to Both, or
• keep it single and use measures like the ones below, which do the filtering themselves.

Only set one result table to Both. Several would make the model ambiguous.

Sort the levels

Text levels sort alphabetically by default, so HIGH would come before LOW. The Hazards query adds an exposure_order column for this. In the Table view, select Hazards[exposure], then Column tools → Sort by column → exposure_order.

Do the same for sensitivity, vulnerability or energy labels if you use them in visuals. The allowed values list each scale from lowest to highest.

Portfolio hierarchy

Portfolios[path] holds the full name, like Norway / Oslo / Office, which works well in slicers and tables. For a drill-down hierarchy, add calculated columns:

Portfolio path ids = PATH ( Portfolios[id], Portfolios[parent_id] )

Level 1 =
LOOKUPVALUE (
    Portfolios[name],
    Portfolios[id], PATHITEM ( Portfolios[Portfolio path ids], 1 )
)

Level 2 =
LOOKUPVALUE (
    Portfolios[name],
    Portfolios[id], PATHITEM ( Portfolios[Portfolio path ids], 2 )
)

Add as many levels as your deepest portfolio, then build a hierarchy from them.

Measures

Add these with Home → New measure. They count only relevant hazards, which is what most climate risk reports should show. See relevance.

Property count = DISTINCTCOUNT ( Properties[id] )

Portfolio value = SUM ( Properties[value_estimate] )

Properties with high exposure =
CALCULATE (
    DISTINCTCOUNT ( Hazards[property_id] ),
    Hazards[relevant] = TRUE (),
    Hazards[exposure] IN { "HIGH", "VERY_HIGH" }
)

Value with high exposure =
SUMX (
    FILTER (
        Properties,
        CALCULATE (
            COUNTROWS ( Hazards ),
            Hazards[relevant] = TRUE (),
            Hazards[exposure] IN { "HIGH", "VERY_HIGH" }
        ) > 0
    ),
    Properties[value_estimate]
)

Share of value with high exposure =
DIVIDE ( [Value with high exposure], [Portfolio value] )

Hazards without sensitivity =
CALCULATE (
    COUNTROWS ( Hazards ),
    Hazards[relevant] = TRUE (),
    ISBLANK ( Hazards[sensitivity] )
)

Properties near sensitive nature =
CALCULATE (
    DISTINCTCOUNT ( Biodiversity[property_id] ),
    Biodiversity[relevant] = TRUE (),
    NOT ISBLANK ( Biodiversity[proximity] )
)

Certificates expiring within a year =
CALCULATE (
    COUNTROWS ( EnergyCertificates ),
    EnergyCertificates[expires_at] >= TODAY (),
    EnergyCertificates[expires_at] < EDATE ( TODAY (), 12 )
)

value_estimate is in the property's local currency, which is NOK for Norwegian properties. Power BI loads the timestamps as date/time values in UTC.

Report ideas

• Risk matrix: a matrix visual with Hazards[name] on rows, Hazards[exposure] on columns and Property count as values, filtered to relevant hazards.
• Value at risk by hazard: a bar chart of Value with high exposure by Hazards[name].
• Portfolio overview: a table of Portfolios[path] with Property count, Portfolio value and Share of value with high exposure.
• Map: the API doesn't return coordinates, but the map visuals can place properties from address, postal_code, postal_area and country. Set each column's Data category in Column tools.
• Assessment backlog: Hazards without sensitivity by portfolio shows where sensitivity assessments are still missing.
• Energy: a bar chart of buildings by EnergyCertificates[label], and a list of certificates expiring within a year.