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:
- On the Home tab, select Enter data.
- Name the table
Indicatorsand select Load. (Leave the single empty column as it is.) - Move the two measures from Chapter 6 into it:
select
Total Visitsin the Data pane, and on the Measure tools tab set Home table toIndicators. Do the same forTotal Target. - Right-click the column in
Indicatorsand select Hide. - 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.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] )
)
-
DISTINCTCOUNTcounts patients, because a patient on ART has many visits. -
USERELATIONSHIPmakes the date table filterart_start_dateinstead of the visit date, for this measure only. -
NOT ISBLANKexcludes 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
USERELATIONSHIPmight 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
-
iso_yearandiso_weekare 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. - Create a Table visual with
dim_date[iso_year],dim_date[iso_week],Facilities ReportingandReporting 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_weekmust always be used withiso_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
- Create a Table visual with
dim_date[quarter],Malaria Tested,Malaria Tested Previous QuarterandMalaria 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.
- Create a second Table visual with
dim_date[month],Total Visits,Visits QTDandVisits 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 useUSERELATIONSHIPorRELATED, 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
REMOVEFILTERSor 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.9is true in a month with no target. TestISBLANKfirst. -
Expecting a filter to travel from one dimension to another. A
state slicer does not filter
dim_patient. Filter through the fact table, as inAverage 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:
- What is the indicator definition in plain language?
- Is the unit a visit, a test, or a person?
- Which table contains the events being counted?
- Which filters should come from the report (slicers, rows)?
- Which rules belong inside the measure (
CALCULATEfilters)? - Is it a ratio? Use
DIVIDE, and check the denominator. - 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.
Power BI for M&E and Public Health Data Analysts · The Growing Analyst