Chapter: 7 DAX Without Fear

For many Power BI users, DAX is where confidence breaks. It looks like code, error messages are unfamiliar, and small changes can produce different numbers.

But if you work in Monitoring and Evaluation or public health, you already think the way DAX works. You define indicators, specify who is counted, apply reporting rules, and interpret numbers in context. DAX formalizes that thinking.

In this chapter you will write the indicators for the book’s dataset. The first half builds them in seven patterns. The second half takes you through the main families of DAX functions (aggregation, counting, logic, filters, iterators, relationships, time intelligence, ranking and text) and builds an indicator with each. Every measure comes with the result you should see, so you can check your work.

7.1 What DAX Is

DAX (Data Analysis Expressions) is the formula language of Power BI models. You use it to:

  • define indicators once, as measures, and reuse them in every visual;
  • control which rows an indicator counts;
  • compare periods and calculate shares, rates and progress against targets.

DAX is not:

  • a data cleaning tool (that is Power Query, Chapter 5);
  • the way to create relationships (you do that in Model view, Chapter 6);
  • a substitute for a good model.

Important principle: DAX works best on top of a well-structured star schema. If a formula feels complicated, check the model first.


7.2 Measures vs Calculated Columns

Both are written in DAX, but they behave differently.

7.2.1 Calculated columns

A calculated column adds a column to a table. Its formula runs once for each row, when the data is loaded or refreshed, and the results are stored in the model.

A calculated column answers: “What is true about this row?”

Example, in dim_patient (select the table, then New column):

Child or Adult = IF ( dim_patient[age] < 15, "Child (<15)", "Adult (15+)" )

Check: 636 patients are Child (<15) and 2,864 are Adult (15+).

Use calculated columns for grouping and classifying, things you put on an axis, in a slicer, or in a filter. In most cases, prefer doing this in Power Query, where the logic sits with the rest of your data preparation.

7.2.2 Measures

A measure is a formula that is evaluated when a visual asks for it, using whatever filters are active at that moment. Nothing is stored; only the formula is saved.

A measure answers: “What is the result for the current selection?”

Your indicators (counts, rates, percentages of target) should be measures.

Rule of thumb: If you want to slice or group by it, use a column. If you want to show a number, use a measure.

7.2.3 Explicit and implicit measures

If you drag a numeric column into a visual, Power BI summarizes it automatically (for example, it sums target). Microsoft calls this an implicit measure; the column shows a sigma (summation) icon in the Data pane. A measure you write yourself is an explicit measure and shows a calculator icon.

Write explicit measures for every indicator. An implicit measure can be summarized differently in different visuals (sum in one, average in another), so the same “indicator” can show different numbers. An explicit measure always calculates the same way.


7.3 Exercise 7.1: Create a Measures Table

As the number of measures grows, keep them in one place. Microsoft’s documentation describes how to create a table that holds only measures and always appears at the top of the Data pane:

  1. On the Home tab, select Enter data.
  2. Name the table Indicators and select Load. (Leave the single empty column as it is.)
  3. Move the two measures from Chapter 6 into it: select Total Visits in the Data pane, and on the Measure tools tab set Home table to Indicators. Do the same for Total Target.
  4. Right-click the column in Indicators and select Hide.
  5. Collapse and reopen the Data pane to see the change.

Create every measure in this chapter in the Indicators table: select Indicators in the Data pane before you select New measure.

To keep a long list tidy, you can group measures into display folders (Model view → select the measure → Properties → Display folder), for example Malaria, HIV, Targets.


7.4 Context: The Core Idea Behind DAX

Most confusion about DAX comes from filter context. Filter context answers one question:

“Which rows are visible right now?”

It comes from slicers, filters on the visual, page or report, the row and column of a table or matrix, and the relationships that carry those filters from dimension tables to fact tables.

A measure does not invent numbers. It calculates over the rows that are visible in its current filter context. That is why the same measure shows a different number in each row of a table: each row has a different filter context.


7.5 Pattern 1: Count Events, and Count People

The simplest measure counts rows in the fact table:

Total Visits = COUNTROWS ( fact_patient_visits )

But M&E indicators often count people, not events. A patient who visits three times is three visits but one person. Use DISTINCTCOUNT on the patient key:

Patients Seen = DISTINCTCOUNT ( fact_patient_visits[patient_id] )

7.5.1 Exercise 7.2: Visits vs People

Create both measures and put them in a Card visual each.

Measure Expected result
Total Visits 10,000
Patients Seen 3,329

Before you build any indicator, ask: is the unit of this indicator a visit, a test, or a person? The answer tells you what to count: rows or distinct patients.


7.6 Pattern 2: Filtered Counts with CALCULATE

CALCULATE evaluates an expression with extra filters added. It answers: “Calculate this, but only for rows where…”

Malaria Tested =
CALCULATE ( [Total Visits], fact_patient_visits[malaria_tested] = "Yes" )
Malaria Positive =
CALCULATE ( [Total Visits], fact_patient_visits[malaria_positive] = "Yes" )

These measures still respect every slicer on the page (state, quarter, facility type). CALCULATE adds the indicator’s rule on top.

Create the rest of the counts the same way:

HIV Tests = CALCULATE ( [Total Visits], fact_patient_visits[hiv_tested] = "Yes" )

HIV Positive = CALCULATE ( [Total Visits], fact_patient_visits[hiv_positive] = "Yes" )

People Tested for HIV =
CALCULATE ( [Patients Seen], fact_patient_visits[hiv_tested] = "Yes" )

TB Screened = CALCULATE ( [Total Visits], fact_patient_visits[tb_screened] = "Yes" )

TB Tested = CALCULATE ( [Total Visits], fact_patient_visits[tb_test_done] = "Yes" )

TB Positive = CALCULATE ( [Total Visits], fact_patient_visits[tb_positive] = "Yes" )

ANC Visits = CALCULATE ( [Total Visits], fact_patient_visits[anc_visit] = "Yes" )

7.6.1 Exercise 7.3: Check the counts

Measure Expected result
Malaria Tested 7,062
Malaria Positive 2,158
HIV Tests 4,941
People Tested for HIV 2,722
HIV Positive 123
TB Screened 4,032
TB Tested 3,241
TB Positive 311
ANC Visits 330

7.6.2 Filtering through a relationship

CALCULATE can also filter on a dimension column. The filter flows to the fact table through the relationship:

ANC Visits (Female) =
CALCULATE ( [ANC Visits], dim_patient[sex] = "Female" )

Expected result: 320. The difference from ANC Visits (330) is the 10 ANC visits recorded for male patients that you found in Exercise 5.11. A measure does not fix bad data; it can only make the rule explicit. Agree with your programme which definition to report, and fix the records at the source.


7.7 Pattern 3: Rates with DIVIDE

Never divide with the / operator in an indicator. Use DIVIDE:

Malaria Positivity Rate = DIVIDE ( [Malaria Positive], [Malaria Tested] )

HIV Positivity Rate = DIVIDE ( [HIV Positive], [HIV Tests] )

TB Positivity Rate = DIVIDE ( [TB Positive], [TB Tested] )

When the denominator is zero or blank, DIVIDE returns BLANK instead of an error. You can give a third argument to return something else, for example DIVIDE ( a, b, 0 ), but think carefully before doing so:

  • BLANK means “no tests were done, so there is no rate”.
  • 0% means “tests were done and none were positive”.

These are very different messages for a programme manager. For rates, keep the default BLANK.

Format each rate as a percentage: select the measure, then on the Measure tools tab set the format to Percentage.

The denominator matters as much as the function. malaria_positive is No both when the test was negative and when no test was done (see the data dictionary). Dividing by [Total Visits] instead of [Malaria Tested] would produce a much lower, wrong rate.

7.7.1 Exercise 7.4: Positivity by state

Create a Table visual with dim_facility[state], Malaria Tested, Malaria Positive and Malaria Positivity Rate.

state Malaria Tested Malaria Positive Positivity
State A 3,125 970 31.0%
State B 2,187 644 29.4%
State C 1,750 544 31.1%
Total 7,062 2,158 30.6%

The total row is not the average of the three rates. The measure recalculates 2,158 ÷ 7,062 for all rows together. This is why indicators must be measures.

Expected overall rates: HIV 2.5% (123 ÷ 4,941) and TB 9.6% (311 ÷ 3,241).


7.8 Variables: Making DAX Readable

Variables (VAR) let you name the parts of a formula, like writing the numerator and denominator of an indicator before dividing:

Malaria Positivity Rate =
VAR Numerator = [Malaria Positive]
VAR Denominator = [Malaria Tested]
RETURN
    DIVIDE ( Numerator, Denominator )

This gives the same result as the one-line version. Variables change clarity, not results, and they make complex measures much easier to check.


7.9 Pattern 4: Share of Total with REMOVEFILTERS

To show what share of all visits each state accounts for, divide by a total that ignores the state filter:

Visit Share =
DIVIDE (
    [Total Visits],
    CALCULATE ( [Total Visits], REMOVEFILTERS ( dim_facility ) )
)

REMOVEFILTERS ( dim_facility ) clears any filter on the facility table (state, district, facility) for the denominator only. Filters on other tables, such as a quarter slicer, still apply.

Check (format as Percentage): State A 44.8%, State B 30.6%, State C 24.5%, total 100%.


7.10 Pattern 5: Comparing Periods (Time Intelligence)

These functions need a date table marked as such, which you did in Chapter 6.

Visits Last Month =
CALCULATE ( [Total Visits], DATEADD ( dim_date[date], -1, MONTH ) )
Visits MoM Change =
VAR CurrentVisits = [Total Visits]
VAR PreviousVisits = [Visits Last Month]
RETURN
    DIVIDE ( CurrentVisits - PreviousVisits, PreviousVisits )
Visits YTD = TOTALYTD ( [Total Visits], dim_date[date] )

7.10.1 Exercise 7.5: Monthly trend table

Create a Table visual with dim_date[month] and the four measures (format Visits MoM Change as Percentage).

month Total Visits Visits Last Month Visits MoM Change Visits YTD
Jan 846 846
Feb 795 846 −6.0% 1,641
Mar 815 795 2.5% 2,456
… … … … …
Dec 835 830 0.6% 10,000

January has no previous month in the data, so Visits Last Month and Visits MoM Change are blank. That is the correct answer, and DIVIDE handles it without an error.

About calendar-based time intelligence. The functions above use what Microsoft now calls classic time intelligence. Microsoft also offers calendar-based time intelligence, which supports week-based calculations (useful for weekly surveillance using ISO weeks) and calendars that are not standard. As of October 2026 it is a preview feature that Microsoft’s documentation recommends. You enable it under File → Options and settings → Options → Preview features → Enhanced DAX Time Intelligence. Preview features can change, so this book uses the classic functions.


7.11 Pattern 6: Targets and Percent of Target

In Chapter 6 you saw that targets appear only in January, April, July and October when you look at months. Microsoft’s guidance for facts stored at a higher grain is to use a measure that returns BLANK below that grain.

Replace the Total Target measure with this one, named Target:

Target =
IF (
    ISFILTERED ( dim_date[date] )
        || ISFILTERED ( dim_date[month] )
        || ISFILTERED ( dim_date[month_num] )
        || ISFILTERED ( dim_date[iso_week] ),
    BLANK (),
    SUM ( fact_targets[target] )
)

ISFILTERED returns TRUE when that column is filtered directly, for example when months are on the rows of a visual. In that case the measure returns BLANK; at quarter or year level it returns the target.

Then create indicator-specific targets and progress:

Malaria Test Target =
CALCULATE ( [Target], fact_targets[indicator] = "Malaria tests" )

Malaria % of Target = DIVIDE ( [Malaria Tested], [Malaria Test Target] )

Finally, hide fact_targets[target] (right-click → Hide) so that nobody can drag the raw column into a visual and bypass the measure. This is also Microsoft’s recommendation for higher-grain facts.

7.11.1 Exercise 7.6: Progress against target by quarter

Create a Table visual with dim_date[quarter], Malaria Tested, Malaria Test Target and Malaria % of Target.

quarter Malaria Tested Malaria Test Target % of Target
Q1 1,738 1,928 90.1%
Q2 1,735 1,946 89.2%
Q3 1,769 1,955 90.5%
Q4 1,820 2,030 89.7%
Total 7,062 7,859 89.9%

Now replace quarter with month. Malaria Test Target and % of Target are blank for every month, which is correct, because no monthly target exists. The same happens with a slicer on individual dates.

Create HIV Test Target and HIV % of Target the same way, using "HIV tests". Check: HIV tests are 1,224, 1,260, 1,244 and 1,213 in Q1–Q4 against targets of 1,357, 1,373, 1,368 and 1,343.


7.12 Pattern 7: Using the Inactive Relationship

In Chapter 6 you created an inactive relationship from art_start_date to dim_date[date]. To count ART initiations by the month they started, not the month of the visit, activate it inside the measure with USERELATIONSHIP:

ART Initiations =
CALCULATE (
    DISTINCTCOUNT ( fact_patient_visits[patient_id] ),
    USERELATIONSHIP ( fact_patient_visits[art_start_date], dim_date[date] ),
    NOT ISBLANK ( fact_patient_visits[art_start_date] )
)
  • DISTINCTCOUNT counts patients, because a patient on ART has many visits.
  • USERELATIONSHIP makes the date table filter art_start_date instead of the visit date, for this measure only.
  • NOT ISBLANK excludes visits without a start date. Without it, the grand total would count every patient in the table.

For comparison:

Patients on ART =
CALCULATE ( [Patients Seen], fact_patient_visits[art_started] = "Yes" )

7.12.1 Exercise 7.7: ART initiations by month

Create a Table visual with dim_date[month] and ART Initiations.

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Total
14 13 16 11 4 1 3 9 3 4 3 81

December is blank: no patient started ART in December.

Patients on ART shows 82. The difference of one is a patient whose only recorded start date was the placeholder 1900-01-01, which you replaced with null in Exercise 5.7. That patient is on ART, but their start date is unknown, so they cannot be counted in any month. This is exactly the kind of effect a cleaning decision has on an indicator, and why Exercise 5.10 asked you to document it.

Note: Microsoft’s row-level security documentation warns that USERELATIONSHIP might cause errors when row-level security is enabled. If you add security roles (Chapter 9), test this measure, and see the alternative described there.


7.13 The DAX Function Families

The seven patterns above cover the indicators most M&E reports need, using about fifteen functions. DAX has more than 250. You do not need to learn them one by one: they fall into families, and each family answers one kind of question. When an indicator needs something new, the question you are asking tells you which family to look in.

Family The question it answers Functions you will use most
Aggregation What is the total, average, lowest or highest value? SUM, AVERAGE, MEDIAN, MIN, MAX
Counting How many rows, values or unique things? COUNTROWS, DISTINCTCOUNT, COUNT, COUNTBLANK
Logical Which rule applies? IF, SWITCH, AND, OR, IN, COALESCE
Filter Which rows should the calculation see? CALCULATE, FILTER, REMOVEFILTERS, ALLSELECTED, KEEPFILTERS
Iterators What is the result if I work row by row? SUMX, AVERAGEX, MINX, MAXX
Relationship How do I use a column from another table? RELATED, USERELATIONSHIP
Time intelligence How does this period compare with another? DATEADD, TOTALQTD, PREVIOUSQUARTER, SAMEPERIODLASTYEAR, DATESBETWEEN
Ranking Which item is first, and which is last? RANKX, TOPN
Text How do I put a number into words? FORMAT, CONCATENATEX, &
Information What is selected or visible right now? ISBLANK, ISFILTERED, ISINSCOPE, HASONEVALUE

The families broadly follow the categories in Microsoft’s DAX function reference. Counting and iterators get their own families here because you will use them so often.

Each family below lists its main functions and builds one or two indicators with them, with the result you should see. Create every measure in the Indicators table, as before.

7.13.1 Aggregation: totals, averages and extremes

Aggregation functions reduce a column to one number, using only the rows visible in the current filter context. You have already used SUM for targets.

Function Returns
SUM The total of a numeric column
AVERAGE The mean of a numeric column
MEDIAN The middle value, which extreme values affect less than the mean
MIN, MAX The lowest and highest value. They also work on dates
Average Patient Age = AVERAGE ( dim_patient[age] )

Median Patient Age = MEDIAN ( dim_patient[age] )

First ART Start = MIN ( fact_patient_visits[art_start_date] )

Latest ART Start = MAX ( fact_patient_visits[art_start_date] )

Check: average age 40.1, median age 40, first ART start 4 January 2024, latest ART start 23 November 2024. On the Measure tools tab, give the two ART measures a date format.

Filters do not travel between dimensions. Put Average Patient Age in a table with dim_facility[state]. Every row shows 40.1. The measure is not wrong. age is in dim_patient, and filters flow from a dimension to a fact table, never from one dimension to another (Chapter 6). The state filter reaches the visits but not the patients, so the measure averages all 3,500 patients in every row. By dim_patient[sex], which is in the same table as age, it does change: Female 40.1, Male 40.2.

To average only the patients who had a visit in the current selection, use the visits table as a filter:

Average Age of Patients Seen =
CALCULATE ( AVERAGE ( dim_patient[age] ), fact_patient_visits )

When a fact table is used as a filter in CALCULATE, only the patients who appear in its visible rows are kept. Check: 40.2 overall (the 3,329 patients seen); State A 40.0, State B 40.7, State C 39.8.

7.13.2 Counting: rows, values and data-quality checks

You used COUNTROWS and DISTINCTCOUNT in Pattern 1. The family has a few more members.

Function Counts
COUNTROWS Rows in a table
DISTINCTCOUNT Different values in a column. A blank counts as one value; DISTINCTCOUNTNOBLANK leaves it out
COUNT, COUNTA Values in a column that are not blank
COUNTBLANK Blank values in a column

Counting functions can turn the checks you made in Power Query into measures that run again on every refresh:

TB Test Dates Recorded = COUNT ( fact_patient_visits[tb_test_date] )

ART Visits Missing Regimen =
CALCULATE (
    COUNTBLANK ( fact_patient_visits[art_regimen] ),
    fact_patient_visits[art_started] = "Yes"
)

ART Visits Missing Start Date =
CALCULATE (
    COUNTBLANK ( fact_patient_visits[art_start_date] ),
    fact_patient_visits[art_started] = "Yes"
)

Check: TB Test Dates Recorded is 3,241, the same as TB Tested. If the two ever differ after a refresh, a test was recorded without a date, or a date without a test. ART Visits Missing Regimen is 4 and ART Visits Missing Start Date is 6: the problems you found in Exercise 5.10. On a data-quality page, these measures show at a glance whether next month’s file has the same problems.

Reporting completeness. In surveillance and routine reporting, programmes track how many facilities reported in each period:

Facilities Reporting =
DISTINCTCOUNT ( fact_patient_visits[facility_id] )

Reporting Rate =
DIVIDE ( [Facilities Reporting], COUNTROWS ( dim_facility ) )

COUNTROWS ( dim_facility ) is the number of facilities expected to report: all 60, or fewer when you filter by state or district. In this dataset, a facility counts as reporting if it recorded at least one visit in the period. In your own programme, count the reports actually received.

Exercise 7.8: Weekly reporting completeness

  1. iso_year and iso_week are whole numbers, so a visual would add them up. Select each one in the Data pane and, on the Column tools tab, set Summarization to Don’t summarize.
  2. Create a Table visual with dim_date[iso_year], dim_date[iso_week], Facilities Reporting and Reporting Rate (format as Percentage).

What you should see:

  • All 60 facilities report in every month, but not in every week. The lowest full week is week 22 of 2024: 52 facilities, 86.7%. Every other full week has between 55 and 60.
  • The last row, week 1 of ISO year 2025, shows 35 facilities (58.3%). That week contains only 30 and 31 December 2024, because the data ends there. A partial week is not a reporting failure, and it is the reason iso_week must always be used with iso_year.
  • The total is 60 facilities, 100%.

7.13.3 Logical: rules and categories

Function Use it to
IF Return one result when a condition is true and another when it is false
SWITCH Choose between several results. Easier to read than IF inside IF
AND, OR, NOT Combine or reverse conditions
IN Test whether a value is in a list, such as { "TLD", "TLE" }
COALESCE Return the first value that is not blank

In formulas, most people write && instead of AND and || instead of OR. They do the same job and, unlike the functions, can join more than two conditions.

IN: a list of values. Many HIV programmes track the share of patients on regimens that contain dolutegravir (DTG). In this dataset, those are TLD and ABC-3TC-DTG:

Patients on DTG Regimen =
CALCULATE (
    [Patients Seen],
    fact_patient_visits[art_regimen] IN { "TLD", "ABC-3TC-DTG" }
)

Patients with Regimen Recorded =
CALCULATE (
    [Patients Seen],
    NOT ISBLANK ( fact_patient_visits[art_regimen] )
)

DTG Regimen Share =
DIVIDE ( [Patients on DTG Regimen], [Patients with Regimen Recorded] )

Check: 65 of 80 patients, 81.3%. The denominator is the 80 patients with a regimen recorded, not the 82 Patients on ART: two patients on ART have no regimen at any visit, the gap you found in Exercise 5.10. Either denominator can be defended, but the report must say which one it uses.

SWITCH: from a number to a status. A programme manager scanning 60 facilities wants a word, not a percentage:

Malaria Target Status =
VAR Progress = [Malaria % of Target]
RETURN
    SWITCH (
        TRUE (),
        ISBLANK ( Progress ), BLANK (),
        Progress >= 0.9, "On track",
        Progress >= 0.85, "Behind",
        "Off track"
    )

SWITCH ( TRUE (), … ) checks each condition from top to bottom and returns the result of the first one that is true. The last value, "Off track", is used when none is. The ISBLANK line comes first on purpose: in a comparison, blank is treated as zero, so without it every month (where the target is blank) would show “Off track”.

Check: add Malaria Target Status to the table from Exercise 7.6. Q1 is On track (90.1%), Q2 Behind (89.2%), Q3 On track (90.5%), Q4 Behind (89.7%) and the total Behind (89.9%). By facility, for the whole year, 28 facilities are on track, 23 are behind and 9 are off track.

The cut-offs of 90% and 85% are examples. Agree them with your programme and write them into the indicator definition.

COALESCE: zero instead of blank. COALESCE ( [HIV Positive], 0 ) returns 0 where HIV Positive is blank. Use it for counts where zero is a real answer, not for rates (Pattern 3). Be aware that a measure that never returns blank makes visuals show a row for every facility and period, including those with no data at all.

7.13.4 Filter: controlling what a calculation sees

You used CALCULATE in Pattern 2 and REMOVEFILTERS in Pattern 4. Filter functions are the most important family in DAX, because they change the filter context itself.

Function Use it to
CALCULATE Evaluate an expression with filters added, replaced or removed
FILTER Keep the rows of a table that meet a condition, checking one row at a time
ALL, REMOVEFILTERS Ignore the filters on a table or column
ALLEXCEPT Ignore the filters on a table, except on the columns you name
ALLSELECTED Ignore the rows and columns of the visual, but keep slicers and filters from outside it
KEEPFILTERS Add a filter in CALCULATE without replacing the one already on that column
VALUES Return the values of a column that are visible in the current context

CALCULATE replaces filters on the same column. This is the behaviour that surprises people most.

State A Visits =
CALCULATE ( [Total Visits], dim_facility[state] = "State A" )

State A Visits (Keep Filters) =
CALCULATE (
    [Total Visits],
    KEEPFILTERS ( dim_facility[state] = "State A" )
)

Exercise 7.9: When CALCULATE replaces a filter

Create a Table visual with dim_facility[state] and both measures.

state State A Visits State A Visits (Keep Filters)
State A 4,483 4,483
State B 4,483
State C 4,483
Total 4,483 4,483

In the State B row, the visual filters state to State B. The first measure’s filter is also on state, so it replaces State B with State A. KEEPFILTERS keeps both filters at once. No facility is in State B and State A, so the result is blank.

The same is true of every measure from Pattern 2. A slicer on malaria_tested set to No does not change Malaria Tested, because the measure’s own filter on that column replaces the slicer.

When you have seen this, delete the two measures. They were only a demonstration.

FILTER: rules that are more than one column. FILTER goes through a table row by row and keeps the rows where a condition is true. Use it when a rule is not a simple column = value: for example, an OR across several columns, or a condition on a measure.

Visits with Any Test =
COUNTROWS (
    FILTER (
        fact_patient_visits,
        fact_patient_visits[malaria_tested] = "Yes"
            || fact_patient_visits[hiv_tested] = "Yes"
            || fact_patient_visits[tb_test_done] = "Yes"
    )
)

Check: 9,017 visits (90.2%) included at least one malaria, HIV or TB test.

The next measure counts facilities below 90% of their malaria testing target. VALUES returns the facilities in the current context, and for each one, FILTER calculates [Malaria % of Target] as if that facility alone were selected:

Facilities Below 90% of Malaria Target =
COUNTROWS (
    FILTER (
        VALUES ( dim_facility[facility_id] ),
        VAR Progress = [Malaria % of Target]
        RETURN
            NOT ISBLANK ( Progress ) && Progress < 0.9
    )
)

Check: 32 facilities for the year, the 23 “Behind” and 9 “Off track” facilities from Malaria Target Status (State A 16, State B 8, State C 8). By quarter: Q1 31, Q2 32, Q3 29, Q4 31. By month the measure is blank, because no monthly target exists; the NOT ISBLANK test makes sure of that.

ALLSELECTED: share of what is selected. Visit Share (Pattern 4) divides by the visits in all states. To divide by the states the user has selected instead:

Visit Share of Selection =
DIVIDE (
    [Total Visits],
    CALCULATE ( [Total Visits], ALLSELECTED ( dim_facility ) )
)

Check: add a slicer on dim_facility[state], select State A and State B, and show both share measures by state. Visit Share shows 44.8% and 30.6% (total 75.5%). Visit Share of Selection shows 59.4% and 40.6% (total 100%).

7.13.5 Iterators: working row by row

An iterator goes through a table one row at a time, calculates an expression for each row, and then combines the results. Most iterators end in X. FILTER is also an iterator: it keeps rows instead of combining results.

When the expression contains a measure, the measure is calculated for each row as if that row were selected in a slicer. That is how one formula can work out a result for every patient, month or facility, and then summarize them.

Function Calculates the expression for each row, then returns
SUMX The sum of the results
AVERAGEX The average of the results
MINX, MAXX The lowest or highest result
COUNTX The number of results that are not blank
Average Visits per Patient =
AVERAGEX (
    VALUES ( fact_patient_visits[patient_id] ),
    [Total Visits]
)

Busiest Month Visits =
MAXX ( VALUES ( dim_date[month] ), [Total Visits] )

Quietest Month Visits =
MINX ( VALUES ( dim_date[month] ), [Total Visits] )

Check: 3.00 visits per patient (10,000 ÷ 3,329); busiest month 878 (May); quietest month 795 (February). On a dashboard, these give the range against which a single month can be judged.

A turnaround time. An iterator can calculate a value for each row that does not exist in any column. A TB sample is collected on the day of the visit or up to three days later, and the number of days between the two is a turnaround time:

Average Days to TB Sample =
AVERAGEX (
    FILTER (
        fact_patient_visits,
        NOT ISBLANK ( fact_patient_visits[tb_test_date] )
    ),
    DATEDIFF (
        RELATED ( dim_date[date] ),
        fact_patient_visits[tb_test_date],
        DAY
    )
)

FILTER keeps only the visits with a TB test date. For each one, RELATED reads the visit date from dim_date (see the next family), DATEDIFF counts the days from the visit to the sample, and AVERAGEX averages the results.

Check: 1.49 days overall; State A 1.50, State B 1.54, State C 1.44. The same pattern measures the days from sample to result, or from diagnosis to the start of treatment, when your data has those dates.

7.13.6 Relationship: reaching another table

Function Use it to
RELATED Read a column from the “one” side of a relationship (a dimension) while working row by row on the “many” side (a fact table)
RELATEDTABLE Do the opposite: return the matching fact rows for each row of a dimension
USERELATIONSHIP Use an inactive relationship for one calculation (Pattern 7)
CROSSFILTER Change a relationship’s filter direction for one calculation. Use it rarely, and check the model first

RELATED needs a row to start from, so you use it inside an iterator or in a calculated column, as in Average Days to TB Sample. Another use is finding when each facility last recorded a visit:

Last Visit Date =
MAXX ( fact_patient_visits, RELATED ( dim_date[date] ) )

Days Since Last Visit =
DATEDIFF ( [Last Visit Date], MAX ( dim_date[date] ), DAY )

MAX ( dim_date[date] ) is the last date in the current selection: 31 December 2024 when nothing is filtered. In a report on live data, use TODAY () instead, to count the days up to today.

Check: create a Table visual with dim_facility[facility_name], Last Visit Date (with a date format) and Days Since Last Visit, and sort it by Days Since Last Visit, largest first. Facility 31 last recorded a visit on 18 December 2024 (13 days), Facility 37 on 22 December (9 days) and Facility 28 on 23 December (8 days). A facility that stops recording visits is worth a phone call: the cause may be a reporting problem, a stock-out or a closure.

7.13.7 Time intelligence: comparing periods

Pattern 5 used DATEADD and TOTALYTD. Most time intelligence functions return a set of dates, which CALCULATE then uses as the filter. All of them need the marked date table from Chapter 6.

Function Returns the dates of
DATEADD The current period, moved back or forward by days, months, quarters or years
PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR The whole previous month, quarter or year
SAMEPERIODLASTYEAR The same period one year earlier
DATESMTD, DATESQTD, DATESYTD The month, quarter or year to date. TOTALMTD, TOTALQTD and TOTALYTD are shortcuts that also do the calculation
DATESBETWEEN Every date from a start date to an end date
DATESINPERIOD A number of days, months, quarters or years, counted from one date
Visits QTD = TOTALQTD ( [Total Visits], dim_date[date] )

Malaria Tested Previous Quarter =
CALCULATE ( [Malaria Tested], PREVIOUSQUARTER ( dim_date[date] ) )

Malaria Tested QoQ Change =
VAR ThisQuarter = [Malaria Tested]
VAR LastQuarter = [Malaria Tested Previous Quarter]
RETURN
    DIVIDE ( ThisQuarter - LastQuarter, LastQuarter )

A rolling three-month total smooths out a noisy monthly series, which helps when you watch trends in surveillance data:

Visits Rolling 3 Months =
VAR LastDate = MAX ( dim_date[date] )
VAR FirstDate = EOMONTH ( LastDate, -3 ) + 1
RETURN
    CALCULATE (
        [Total Visits],
        DATESBETWEEN ( dim_date[date], FirstDate, LastDate )
    )

EOMONTH ( LastDate, -3 ) is the last day of the month three months back, so FirstDate, one day later, is the first day of the window. For April, the window runs from 1 February to 30 April. DATESINPERIOD with -3, MONTH looks simpler, but it counts back from the exact date. From 30 April it reaches 30 January, so the window would start on 31 January and include one day of January.

Year on year is the usual comparison in annual reporting:

Visits Same Period Last Year =
CALCULATE ( [Total Visits], SAMEPERIODLASTYEAR ( dim_date[date] ) )

The book’s data covers 2024 only, so this measure is blank everywhere, which is the correct answer. Once your data and dim_date cover two years, it shows the previous year’s figure for every month, quarter or year.

Exercise 7.10: Quarters and rolling months

  1. Create a Table visual with dim_date[quarter], Malaria Tested, Malaria Tested Previous Quarter and Malaria Tested QoQ Change (format as Percentage).
quarter Malaria Tested Previous Quarter QoQ Change
Q1 1,738
Q2 1,735 1,738 −0.2%
Q3 1,769 1,735 2.0%
Q4 1,820 1,769 2.9%
Total 7,062

Q1 has no previous quarter in the data. In the total row, PREVIOUSQUARTER looks at the quarter before 1 January 2024, which is also outside the data.

  1. Create a second Table visual with dim_date[month], Total Visits, Visits QTD and Visits Rolling 3 Months.
month Total Visits Visits QTD Visits Rolling 3 Months
Jan 846 846 846
Feb 795 1,641 1,641
Mar 815 2,456 2,456
Apr 809 809 2,419
May 878 1,687 2,502
Jun 802 2,489 2,489
… … … …
Dec 835 2,531 2,531

Visits QTD starts again in each quarter. Visits Rolling 3 Months always covers three months, except in January and February, which have fewer months of data behind them. In the total row, both show 2,531: the last date of the year is 31 December, so both look at October to December.

7.13.8 Ranking: first, last and top N

Function Use it to
RANKX Give each item its position when sorted by a measure
TOPN Return the first N rows of a table, sorted by a measure
ISINSCOPE Check whether the visual is grouping by a column in the current row (an information function)
Facility Rank (Malaria Tested) =
IF (
    ISINSCOPE ( dim_facility[facility_name] ),
    RANKX (
        ALLSELECTED ( dim_facility[facility_name] ),
        [Malaria Tested]
    )
)

RANKX compares each facility’s Malaria Tested with every facility in the selection. ISINSCOPE leaves the total row blank, where a rank has no meaning: IF without a third argument returns blank when the condition is false.

Exercise 7.11: Rank the facilities

Create a Table visual with dim_facility[facility_name], Malaria Tested and Facility Rank (Malaria Tested), sorted by the rank.

facility_name Malaria Tested Rank
Facility 2 151 1
Facility 26 136 2
Facility 4 134 3
Facility 11 133 4
Facility 40 133 4
Facility 45 130 6

Facility 11 and Facility 40 tie at 133, so both rank 4 and the next facility ranks 6. To rank it 5 instead, give RANKX its optional arguments (the empty one is the value to rank, which defaults to the current row; then the sort order and the way ties are handled):

RANKX (
    ALLSELECTED ( dim_facility[facility_name] ),
    [Malaria Tested], , DESC, DENSE
)

Now add a slicer on dim_facility[state] and select State A. The ranks start again within the selection: Facility 4 (134) is 1, Facility 21 (129) is 2 and Facility 53 (127) is 3.

For a chart of the top ten facilities, you do not need TOPN. In the Filters pane, set the filter type of facility_name to Top N, show the top 10, and drag Malaria Tested into By value. Use TOPN inside measures, when a calculation needs the top items.

7.13.9 Text: putting numbers into words

Function Use it to
& Join pieces of text
FORMAT Turn a number or date into text with a format, such as "0.0%" or "d mmm yyyy"
SELECTEDVALUE Return the value selected in a column, or an alternative when there is no single value
CONCATENATEX Join the values from the rows of a table into one text, with a separator

A title that follows the slicers:

Malaria Title =
"Malaria positivity, "
    & SELECTEDVALUE ( dim_facility[state], "all states" )
    & ": "
    & FORMAT ( [Malaria Positivity Rate], "0.0%" )

To use it as a visual’s title, select the visual, open Format visual → General → Title, select fx next to Text, set Format style to Field value, and choose Malaria Title.

Check: with no state selected, the title reads Malaria positivity, all states: 30.6%. Select State B and it reads Malaria positivity, State B: 29.4%. SELECTEDVALUE returns the alternative both when nothing is selected and when more than one state is selected. If users can select several states, choose wording that fits both cases, such as “selected states”.

A list of names:

Facilities Not Reporting =
CONCATENATEX (
    FILTER (
        VALUES ( dim_facility[facility_name] ),
        ISBLANK ( [Total Visits] )
    ),
    dim_facility[facility_name],
    ", ",
    dim_facility[facility_name], ASC
)

Exercise 7.12: Who did not report?

Add Facilities Not Reporting to the weekly table from Exercise 7.8, and widen the column so the names are readable.

iso_year iso_week Facilities Reporting Facilities Not Reporting
2024 1 57 Facility 25, Facility 37, Facility 59
2024 2 58 Facility 17, Facility 22
2024 22 52 Facility 10, Facility 16, Facility 27, Facility 29, Facility 30, Facility 38, Facility 41, Facility 50

In the total row the list is empty, because every facility reported at least once in the year. A table like this one, filtered to last week, tells a district officer whom to call on Monday morning.

7.13.10 Information: what is in the current context?

You have already met most of this family. Information functions tell a measure about the context it is in, so that it can decide what to return.

Function Returns TRUE when Used in
ISBLANK A value is blank Pattern 7, Malaria Target Status
ISFILTERED A column is filtered directly Target (Pattern 6)
ISINSCOPE The visual groups by a column in the current row Facility Rank (Malaria Tested)
HASONEVALUE Exactly one value of a column is visible An older way to write SELECTEDVALUE

7.13.11 Finding the rest

DAX also has maths functions (DIVIDE, ROUND, ABS), statistical functions (PERCENTILE.INC, STDEV.S) and table functions (SUMMARIZECOLUMNS, ADDCOLUMNS) that build whole tables. When you need something new, start from the family. Microsoft’s DAX function reference, listed in the references at the end of this book, groups every function by category, with its syntax and examples.


7.14 Testing Measures in DAX Query View

Instead of building a visual for every check, you can query the model directly. Select the DAX query view icon on the left side of Power BI Desktop and run:

EVALUATE
SUMMARIZECOLUMNS (
    dim_facility[state],
    "Malaria Tested", [Malaria Tested],
    "Malaria Positive", [Malaria Positive],
    "Positivity", [Malaria Positivity Rate]
)

The result should match Exercise 7.4. (The results grid does not apply measure formats, so rates appear as decimals, for example 0.3104.)

You can also draft a measure in DAX query view with DEFINE MEASURE, test it, and then select Update model with changes to save it to the model.


7.15 Newer DAX Features Worth Knowing

These features are part of Power BI as of October 2026. You do not need them to complete this book.

  • Visual calculations. A calculation defined on a single visual rather than in the model, written against what the visual shows. For example, a running total on a monthly table: Running Visits = RUNNINGSUM ( [Total Visits] ). Templates are available for running sums, moving averages and percent of total. Visual calculations cannot use USERELATIONSHIP or RELATED, and are stored only on that visual, so keep indicators as model measures.
  • DAX user-defined functions (UDFs). Generally available since the June 2026 release. A UDF packages reusable logic, for example one standard way to calculate a rate, that many measures can call. You define them in DAX query view, TMDL view or Model view.
  • Calendar-based time intelligence (preview), described in Pattern 5.

7.16 Common DAX Mistakes

Most DAX problems are not syntax errors. They are thinking errors:

  • Counting visits when the indicator counts people (or the reverse). Decide the unit first.
  • The wrong denominator. A positivity rate divides by tests done, not by visits.
  • Using DAX to compensate for a weak model. If you need many REMOVEFILTERS or bi-directional tricks, revisit the model.
  • Pre-calculating rates as columns. A rate stored on each row cannot be re-aggregated correctly for a district or quarter.
  • Forgetting that blank counts as zero in a comparison. [Malaria % of Target] < 0.9 is true in a month with no target. Test ISBLANK first.
  • Expecting a filter to travel from one dimension to another. A state slicer does not filter dim_patient. Filter through the fact table, as in Average Age of Patients Seen.
  • Copying formulas from the internet without checking the table and column names and the context they were written for.

7.17 A Simple DAX Checklist

Before writing any measure, answer:

  1. What is the indicator definition in plain language?
  2. Is the unit a visit, a test, or a person?
  3. Which table contains the events being counted?
  4. Which filters should come from the report (slicers, rows)?
  5. Which rules belong inside the measure (CALCULATE filters)?
  6. Is it a ratio? Use DIVIDE, and check the denominator.
  7. What should the measure show when there is no data: blank or zero?

7.18 Measures Created in This Chapter

Measure Purpose
Total Visits, Patients Seen Visits and unique patients
Malaria Tested, Malaria Positive, Malaria Positivity Rate Malaria testing
HIV Tests, People Tested for HIV, HIV Positive, HIV Positivity Rate HIV testing
TB Screened, TB Tested, TB Positive, TB Positivity Rate TB cascade
ANC Visits, ANC Visits (Female) Antenatal care
Visit Share Share of total visits
Visits Last Month, Visits MoM Change, Visits YTD Trends
Target, Malaria Test Target, Malaria % of Target, HIV Test Target, HIV % of Target Targets
ART Initiations, Patients on ART ART
Average Patient Age, Median Patient Age, Average Age of Patients Seen Patient age
First ART Start, Latest ART Start ART start dates
TB Test Dates Recorded, ART Visits Missing Regimen, ART Visits Missing Start Date Data-quality checks
Facilities Reporting, Reporting Rate, Facilities Not Reporting Reporting completeness
Patients on DTG Regimen, Patients with Regimen Recorded, DTG Regimen Share ART regimens
Malaria Target Status, Facilities Below 90% of Malaria Target Following up targets
Visits with Any Test, Visit Share of Selection Filters
Average Visits per Patient, Busiest Month Visits, Quietest Month Visits Row-by-row summaries
Average Days to TB Sample Turnaround time
Last Visit Date, Days Since Last Visit Facility activity
Visits QTD, Visits Rolling 3 Months, Malaria Tested Previous Quarter, Malaria Tested QoQ Change, Visits Same Period Last Year Comparing periods
Facility Rank (Malaria Tested) Ranking
Malaria Title Dynamic titles

7.19 What to Carry Forward

DAX is the language of explicit definitions. When every indicator is a measure with a clear numerator, denominator and rule, the numbers stay consistent wherever they appear, and you can explain each one.

The next chapter uses these measures to design dashboards that communicate clearly and support decisions.

© 2025–2026 Oluwatobi Olatunbosun · CC BY-NC-SA 4.0
Power BI for M&E and Public Health Data Analysts · The Growing Analyst