DAX measures p3

• Power BI Desktop
• PostgreSQL with PostGIS
• 2 hours

Prerequisites: The data model (p2)

Write DAX measures that stay right under any filter, on the model The data model, p2 builds: counts, a total with the filters changed, and a ratio of two facts. The ratio here is sample records divided by forest area, named for what it is. Each DAX query is shown with its PostGIS twin, the table Power BI should return, computed from the same star Star schemas, d6 builds.

Concept

Measures that stay right under any filter. A measure is a formula Power BI evaluates in the current filter context: the filters a cell of a visual sits under, from slicers, the visual's rows and columns, and the relationships p2 set. A measure isn't stored. A calculated column is: it is computed once per row when the data loads, so a slicer can't change it. Four functions do most of the work:

• COUNTROWS ( table ) counts the table's rows the filters leave visible; DISTINCTCOUNT ( column ) counts a column's distinct visible values.
• CALCULATE ( expression, filter, … ) evaluates an expression under changed filters. A filter such as dim_habitat_class[level1_name] = "Forest" replaces any filter on that column; ALL ( dim_country ) removes every filter on the table, which gives a total to divide by. REMOVEFILTERS is the same thing under a clearer name.
• DIVIDE ( numerator, denominator ) returns blank, not an error, when the denominator is 0 or blank. Blank isn't zero: a visual leaves out a row whose measures are all blank, and blank times a number is still blank.
• A ratio of two facts with different grains (one row per record; one row per country and habitat class) works because both are filtered by the same dim_country, and only through what they share.

The ratio this module builds is sample records divided by forest area: the records of critwire's sample in a country for each 1,000 km² of its forest. Say exactly that, because the name invites more. It isn't sampling effort, nor records made in forest: the numerator counts every record in the country, in forest or not, and the sample holds at most 2,000 records per species, so a country where a much-recorded species lives holds fewer of its records than GBIF does. Nor is it how many animals live there: records say where someone recorded a species, not how many there are.

The data: fact_records, fact_country_habitat, dim_habitat_class and dim_country, with p2's relationships. The habitat fact holds estimates for every country the habitat map covers: 246 of the 251 rows of dim_country.

Worked example

Three measures and one query, for every country with forest. Forest km2 sums the habitat fact with the class filter replaced by forest; the ratio divides with DIVIDE. ADDCOLUMNS ( VALUES ( dim_country[country_code] ), … ) makes one row per country code and evaluates each measure on it, with that row's country as the filter: a measure used inside a row turns the row into a filter (context transition). FILTER keeps the countries with forest.

DEFINE
    MEASURE fact_records[Records] = COUNTROWS ( fact_records )
    MEASURE fact_country_habitat[Forest km2] =
        CALCULATE ( SUM ( fact_country_habitat[est_area_km2] ), dim_habitat_class[level1_name] = "Forest" )
    MEASURE fact_records[Sample records per 1,000 km2 of forest] =
        VAR forest = [Forest km2]
        RETURN IF ( NOT ISBLANK ( forest ), 1000 * DIVIDE ( COALESCE ( [Records], 0 ), forest ) )
EVALUATE
FILTER (
    ADDCOLUMNS (
        VALUES ( dim_country[country_code] ),
        "Records", COALESCE ( [Records], 0 ),
        "Forest km2", [Forest km2],
        "Sample records per 1,000 km2 of forest", [Sample records per 1,000 km2 of forest]
    ),
    NOT ISBLANK ( [Forest km2] )
)
ORDER BY dim_country[country_code]

The table it should return, from the same star in PostGIS. NULLIF ( h.forest_km2, 0 ) turns a zero area into unknown, so the division gives NULL where DIVIDE gives blank, instead of an error.

Checked 2026-11-02 on PostgreSQL 16.15, PostGIS 3.4.2, GEOS 3.12.1, GDAL 3.8.4.

SELECT h.country_code,
       coalesce(r.records, 0) AS records,
       h.forest_km2,
       round(1000 * coalesce(r.records, 0) / NULLIF(h.forest_km2, 0), 3) AS sample_records_per_1000_km2_forest
FROM (SELECT f.country_code, sum(f.est_area_km2) AS forest_km2
      FROM fact_country_habitat AS f
      JOIN dim_habitat_class AS c ON c.habitat_code = f.habitat_code
      WHERE c.level1_name = 'Forest'
      GROUP BY f.country_code) AS h
LEFT JOIN (SELECT country_code, count(*) AS records FROM fact_records GROUP BY country_code) AS r
  ON r.country_code = h.country_code
ORDER BY h.country_code;

-- result: p3-check · 215 rows · sha256 a26cfe30c3ed
country_code | records | forest_km2 | sample_records_per_1000_km2_forest
AD | 0 | 179.18 | 0.000
AE | 0 | 13.11 | 0.000
AF | 0 | 2985.10 | 0.000
AG | 0 | 51.59 | 0.000
AI | 0 | 0.12 | 0.000
… 210 more rows

Lab

1. Run p3-measures in DAX query view and compare it with p3-check, row by row. Power BI shows the ratio with every decimal; the twin rounds it to 3 places.

2. Make the measures part of the model, as p2 did: a DEFINE MEASURE lasts for one query, and the visuals need model measures. Records is there from p2. Select fact_country_habitat in the Data pane, Home → New measure, and type Forest km2 = and its formula from p3-measures in the formula bar; then add the ratio the same way on fact_records, and give it 3 decimal places (Measure tools → Format).

3. A table visual with dim_country[name], Records, Forest km2 and the ratio, sorted by the ratio, highest first. Mauritania heads it with 20,000: 2 records over 0.10 km² of forest. A ratio over a tiny denominator is noise; 22 countries have less than 10 km² of forest. Add a filter on this visual, Forest km2 is greater than or equal to 1,000: Benin now heads the list with 230.269 (961 records, 4,173.38 km²), then Kenya and South Africa.

4. Add a slicer with dim_species[common_name] and choose Jaguar. The records change and the forest doesn't, because dim_species filters fact_records only (p2): Costa Rica shows 94 records and 4.050, Brazil 1,370 records and 0.320. The ratio is now that species' sample records per 1,000 km² of forest. Countries without a jaguar record still show their forest and a ratio of 0, because COALESCE turns their blank count into 0.

5. A calculated column next to the measure. Select dim_country, Table tools → New column, and type Records (column) = COUNTROWS ( RELATEDTABLE ( fact_records ) ). Add it to the table: with Jaguar still chosen, it shows each country's records of every species, because it was computed when the data loaded, before any slicer. Delete the column: a count that ignores the report's filters belongs in a measure, with ALL when the total is what's wanted.

Result

215 rows, one for each country the habitat map gives some forest; 105 of them have sample records. They hold 40,394 of the 40,395 records. The last is in Niue, whose habitat estimates hold no forest, so FILTER leaves it out, with the 30 other countries in the same case. Trinidad and Tobago has 2 records and 2,806.26 km² of forest: 0.713 sample records per 1,000 km². Åland has 187.69 km² of forest and no records: 0. No country's forest area is 0, so NULLIF changes no row here; it is there for the data that will have one.

Exercise

Shares of a total. Write a DAX query that returns, for each continent, the records and the habitat area in km², and each as a share of its total over every country: the continent's value divided by the value with no country filter. Which continents hold a larger share of the records than of the area?

DEFINE
    MEASURE fact_records[Records] = COUNTROWS ( fact_records )
    MEASURE fact_country_habitat[Habitat km2] = SUM ( fact_country_habitat[est_area_km2] )
    MEASURE fact_records[Share of records] =
        DIVIDE ( [Records], CALCULATE ( [Records], ALL ( dim_country ) ) )
    MEASURE fact_country_habitat[Share of area] =
        DIVIDE ( [Habitat km2], CALCULATE ( [Habitat km2], ALL ( dim_country ) ) )
EVALUATE
SUMMARIZECOLUMNS (
    dim_country[continent],
    "Records", [Records],
    "Habitat km2", [Habitat km2],
    "Share of records", [Share of records],
    "Share of area", [Share of area]
)
ORDER BY dim_country[continent]

Its twin, the table it should return, with the shares rounded to 4 places. A window over no partition, sum(…) OVER (), is SQL's total over every row: what ALL gives in DAX.

SELECT c.continent, r.records, h.habitat_km2,
       round(r.records / sum(r.records) OVER (), 4) AS share_of_records,
       round(h.habitat_km2 / sum(h.habitat_km2) OVER (), 4) AS share_of_area
FROM (SELECT DISTINCT continent FROM dim_country) AS c
LEFT JOIN (SELECT d.continent, count(*) AS records
           FROM fact_records AS f JOIN dim_country AS d ON d.country_code = f.country_code
           GROUP BY d.continent) AS r ON r.continent = c.continent
LEFT JOIN (SELECT d.continent, sum(f.est_area_km2) AS habitat_km2
           FROM fact_country_habitat AS f JOIN dim_country AS d ON d.country_code = f.country_code
           GROUP BY d.continent) AS h ON h.continent = c.continent
WHERE r.records IS NOT NULL OR h.habitat_km2 IS NOT NULL
ORDER BY c.continent;

-- result: p3-ex-check · 8 rows · sha256 70c1540a66db
continent | records | habitat_km2 | share_of_records | share_of_area
Africa | 10791 | 29703399.22 | 0.2671 | 0.2026
Antarctica | NULL | 12356486.85 | NULL | 0.0843
Asia | 2985 | 31108178.20 | 0.0739 | 0.2122
Europe | 469 | 23004583.24 | 0.0116 | 0.1569
North America | 9688 | 24217714.23 | 0.2398 | 0.1652
… 3 more rows

Check

The answer: CALCULATE ( [Records], ALL ( dim_country ) ) is the records with every country filter removed, so dividing by it gives the share; the same for the area. Africa, North America, Oceania and South America hold a larger share of the records than of the area, as does Seven seas, on its 7 records; Asia and Europe a smaller one, and Antarctica none. The comparison is the record-to-area ratio of Zonal statistics, s6, by continent: it shows where the sample's species were recorded more than area alone predicts. That follows from which species critwire lists and where people record them, not from where wildlife is.

Eight rows, p2's exercise with two more columns. Europe holds 1.2% of the records and 15.7% of the area, Asia 7.4% and 21.2%; South America 28.0% and 12.1%, Oceania 12.7% and 5.8%. Each share column adds up to 1. Antarctica's share of records is blank, not 0: DIVIDE of a blank count is blank, as the twin's NULL is.

Dialects

• Excel (Windows): the same measures in Excel's Data Model: Power Pivot → Measures → New Measure, the same DAX, shown in a PivotTable on the Data Model. Worksheet formulas such as SUMIFS give the same totals, but each cell names its own criteria; there is no filter context.
• Tableau: an aggregate calculation such as COUNT([Record Id]) / SUM([Est Area Km2]) is evaluated under the view's filters, as a measure is. A level of detail expression does what CALCULATE does: {FIXED : COUNT([Record Id])} is the total with every dimension removed, like ALL. Dividing by zero gives null, as DIVIDE gives blank.
• pandas: group, then divide. records / forest.replace(0, pd.NA) gives missing, not inf, for a zero area, and s / s.sum() a share of the total. Join each grouped fact to the other on country_code, never the facts row by row (p2).
• SQL: p3-check's NULLIF for DIVIDE, and p3-ex-check's sum(…) OVER () for CALCULATE ( …, ALL ( … ) ).

Lab schema: the PostGIS tables

Every column, in words · The diagram alone

Close

Mark done