Counting Boolean Values in DAX Using COUNTA and COUNTAX

What Are We Counting?

The query uses a table named CountTable and demonstrates three different counting expressions. The important part of this example is Column C, where the values can be Boolean or blank.

// Counting Boolean Value

EVALUATE
    CountTable

EVALUATE
    ROW(
        "Col C", COUNTA(CountTable[Column C]),
        "Col CX", COUNTAX(CountTable, COALESCE([Column C], FALSE())),
        "Col A", COUNTA(CountTable[Column A])
    )

1. Displaying the CountTable

Query

EVALUATE
    CountTable

Step-by-step explanation

Step 1: EVALUATE
EVALUATE is used to return a table expression as the result of the DAX query.

Step 2: CountTable
CountTable is the table expression being returned. This allows us to inspect the values in the table before applying the counting expressions.

2. Counting Column C Using COUNTA

Query

COUNTA(CountTable[Column C])

Step-by-step explanation

Step 1: CountTable[Column C]
The expression points directly to Column C in the CountTable table.

Step 2: COUNTA
COUNTA counts the non-blank values in the specified column. Therefore, Boolean values such as TRUE and FALSE are counted because they are values, not blanks.

If Column C contains blank values, those blank values are not counted by COUNTA.

Inside the ROW function

"Col C", COUNTA(CountTable[Column C])

The text “Col C” becomes the name of the output column, while COUNTA(CountTable[Column C]) produces the count.

3. Counting Column C Using COUNTAX and COALESCE

Query

COUNTAX(
    CountTable,
    COALESCE([Column C], FALSE())
)

Step-by-step explanation

Step 1: COUNTAX
COUNTAX evaluates an expression for each row of the specified table and counts the non-blank results of that expression.

Step 2: CountTable
CountTable is the table over which the expression is evaluated row by row.

Step 3: [Column C]
Inside COUNTAX, [Column C] refers to the value of Column C for the current row.

Step 4: COALESCE
COALESCE([Column C], FALSE()) returns the value of [Column C] when it is not blank. If the value is blank, it returns FALSE.

This means that a blank value is converted into a Boolean value before COUNTAX performs the count.

COALESCE([Column C], FALSE())

Because the expression always produces either TRUE or FALSE, the expression is non-blank for every row evaluated by COUNTAX.

4. Counting Column A Using COUNTA

Query

COUNTA(CountTable[Column A])

Step-by-step explanation

Step 1: Select Column A
The expression references CountTable[Column A].

Step 2: Apply COUNTA
COUNTA counts the non-blank values present in Column A.

This demonstrates that COUNTA is not limited to Boolean columns. It can be used to count non-blank values in different types of columns.

"Col A", COUNTA(CountTable[Column A])

The result is returned as a column named Col A by the ROW function.

5. Understanding the ROW Function

Query

ROW(
    "Col C", COUNTA(CountTable[Column C]),
    "Col CX", COUNTAX(CountTable, COALESCE([Column C], FALSE())),
    "Col A", COUNTA(CountTable[Column A])
)

Step-by-step explanation

Step 1: ROW
ROW creates a single-row table from the name and expression pairs provided to it.

Step 2: Col C
The first expression counts non-blank values in Column C using COUNTA.

Step 3: Col CX
The second expression uses COUNTAX and converts blanks in Column C into FALSE using COALESCE.

Step 4: Col A
The third expression counts non-blank values in Column A using COUNTA.

Output structure

6. COUNTA vs COUNTAX with COALESCE

The important difference is not simply COUNTA vs COUNTAX. It is the expression being evaluated by COUNTAX.

When the expression is wrapped with COALESCE, a blank value is replaced by FALSE. Since FALSE is a value and not blank, it can be counted by COUNTAX.

7. Why Use COALESCE for Boolean Values?

Boolean columns can contain three practical states: TRUE, FALSE, and BLANK. Sometimes the business logic requires a blank value to be interpreted as FALSE.

In that situation, COALESCE provides a convenient way to replace blanks with a default Boolean value.

COALESCE([Column C], FALSE())

This expression can be read as:

1. If Column C has a value, return that value.
2. If Column C is blank, return FALSE.

EVALUATE
ROW(
    "Column A Count", COUNTA(CountTable[Column A]),
    "Column C Count", COUNTA(CountTable[Column C]),
    "Column C With Blank as False",
        COUNTAX(
            CountTable,
            COALESCE([Column C], FALSE())
        )
)

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist