DAX Set Functions – EXCEPT, INTERSECT, UNION and COUNTROWS in DAX: Compare, Merge and Count Tables

Understanding the Job Data Scenario

Imagine that job listings are collected every day and stored in separate tables such as
Jan-01, Jan-02, Jan-03,
Jan-04, and Jan-05.

A job may appear on multiple days. Therefore, when analyzing daily job listings,
we may want to identify new jobs, find jobs common to multiple days,
combine daily datasets, or simply count how many jobs were collected each day.

1. Retrieving the Complete Job Table

Query

EVALUATE
    'Jan-01'

Step-by-step explanation

Step 1: EVALUATE
EVALUATE tells DAX to return the result of a table expression.

Step 2: ‘Jan-01’
‘Jan-01’ is the table expression. Since the table itself is supplied,
DAX returns the rows and columns available in that table.

What will you get?

The query returns the complete job dataset available in the Jan-01 table.
This gives us a starting point for understanding the daily job data.

2. Finding Jobs Specific to a Day Using EXCEPT

One common requirement in daily job tracking is to identify jobs that appear on the
latest day but did not appear on the previous day.

For example, we can compare Jan-04 with Jan-03
to identify jobs specific to Jan-04.

Query

EVALUATE
    EXCEPT(
        SELECTCOLUMNS('Jan-04', 
            "Title", 'Jan-04'[Title],
            "Location", 'Jan-04'[Location],
            "Company", 'Jan-04'[Company],
            "Skill", 'Jan-04'[Skills]
        ),
        SELECTCOLUMNS('Jan-03', 
            "Title", 'Jan-03'[Title],
            "Location", 'Jan-03'[Location],
            "Company", 'Jan-03'[Company],
            "Skill", 'Jan-03'[Skills]
        )
    )

Step-by-step explanation

Step 1: SELECTCOLUMNS from Jan-04
The first SELECTCOLUMNS creates a table containing four columns from
Jan-04: Title, Location, Company, and Skill.

Step 2: SELECTCOLUMNS from Jan-03
The second SELECTCOLUMNS creates the same four-column structure from
Jan-03.

Step 3: EXCEPT
EXCEPT compares the first table with the second table and returns
rows that exist in the first table but not in the second table.

Therefore, this query compares the Jan-04 jobs against Jan-03 jobs and returns the
rows that are not present in Jan-03.

Why SELECTCOLUMNS is used here

The two source tables may contain many columns. For the comparison, we only want to
compare the job’s Title, Location,
Company, and Skill.

Both table expressions are therefore shaped into the same column structure before
they are passed to EXCEPT.

Real-world use case

This pattern can be useful for identifying newly appearing job listings
when daily job data is collected repeatedly. It can help reduce repeated listings in
a daily job-analysis workflow.

3. Finding Common Jobs Using INTERSECT

Sometimes, instead of finding new jobs, we want to find jobs that are common to
two different days.

Query

EVALUATE
    INTERSECT(
        SELECTCOLUMNS('Jan-04', 
            "Title", 'Jan-04'[Title],
            "Location", 'Jan-04'[Location],
            "Company", 'Jan-04'[Company],
            "Skill", 'Jan-04'[Skills]
        ),
        SELECTCOLUMNS('Jan-03', 
            "Title", 'Jan-03'[Title],
            "Location", 'Jan-03'[Location],
            "Company", 'Jan-03'[Company],
            "Skill", 'Jan-03'[Skills]
        )
    )

Step-by-step explanation

Step 1: Prepare Jan-04
SELECTCOLUMNS extracts Title, Location, Company, and Skill from
the Jan-04 table.

Step 2: Prepare Jan-03
The second SELECTCOLUMNS creates the same structure from Jan-03.

Step 3: INTERSECT
INTERSECT returns the rows that are common to both table expressions.
In this query, it identifies job records that appear in both Jan-04 and Jan-03
based on the selected columns.

EXCEPT vs INTERSECT

4. Combining Tables Using UNION

What if we want to combine the job listings from two different days into one result?
This is where UNION becomes useful.

Query

EVALUATE
    UNION(
        SELECTCOLUMNS('Jan-04', 
            "Title", 'Jan-04'[Title],
            "Location", 'Jan-04'[Location],
            "Company", 'Jan-04'[Company],
            "Skill", 'Jan-04'[Skills]
        ),
        SELECTCOLUMNS('Jan-03', 
            "Title", 'Jan-03'[Title],
            "Location", 'Jan-03'[Location],
            "Company", 'Jan-03'[Company],
            "Skill", 'Jan-03'[Skills]
        )
    )

Step-by-step explanation

Step 1: Prepare Jan-04
The first SELECTCOLUMNS creates a four-column table from Jan-04.

Step 2: Prepare Jan-03
The second SELECTCOLUMNS creates the same four-column structure
from Jan-03.

Step 3: UNION
UNION combines the rows from the first table expression with the
rows from the second table expression.

In this example, the job rows from Jan-04 and Jan-03 are returned together.
If the same row appears in both source tables, it can appear more than once in
the UNION result.

EXCEPT, INTERSECT and UNION at a glance

5. Counting Jobs in Each Day Using COUNTROWS

After comparing and combining daily job tables, another useful requirement is to
understand how many jobs were collected on each day.

Query

EVALUATE
    VAR JAN1 = COUNTROWS('Jan-01')
    VAR JAN2 = COUNTROWS('Jan-02')
    VAR JAN3 = COUNTROWS('Jan-03')
    VAR JAN4 = COUNTROWS('Jan-04')
    VAR JAN5 = COUNTROWS('Jan-05')

    RETURN
    ROW(
        "Jan 01", JAN1,
        "Jan 02", JAN2,
        "Jan 03", JAN3,
        "Jan 04", JAN4,
        "Jan 05", JAN5
    )

Step-by-step explanation

Step 1: COUNTROWS(‘Jan-01’)
COUNTROWS counts the number of rows in the Jan-01 table.
The result is stored in the variable JAN1.

Step 2: Repeat for the remaining days
The same pattern is used for Jan-02, Jan-03, Jan-04, and Jan-05.
Each count is stored in a separate variable.

Step 3: RETURN
The RETURN statement outputs the final table expression.

Step 4: ROW
ROW creates a single-row table containing the five daily job counts.
Each label such as “Jan 01” is used as an output column name.

What will you get?

The exact numbers depend on the data in your daily tables. The output structure will
look like this:

Understanding the Complete Query Pattern

This example demonstrates an important pattern for working with multiple daily
datasets in DAX. First, we shape the data using SELECTCOLUMNS.
Then, we use set functions to compare or combine those table expressions.

📥 Practice Data:
Download Practice Data Free

🎥 Prefer learning through video?
DAX YouTube Playlist