Dax Function: EXCEPT

Category: Table Manipulation Functions

The EXCEPT function in Power BI returns a table containing rows from the first table that are not present in the second table. It performs a set difference operation, ensuring that only unique rows exclusive to the first table are included in the result.

Purpose

  • To compare two tables and extract rows unique to the first table.

  • Helps in identifying discrepancies or differences between datasets.

  • Simplifies processes involving exclusions in data analysis.

Type of Calculations

  • Performs a row-level comparison between two tables.

  • Excludes rows in the first table that match rows in the second table.

Practical Use Cases

  1. Data Cleansing: Identify rows in a master dataset that are missing in a transactional dataset.

  2. Data Comparison: Compare historical data with current data to find newly added records.

  3. Exclusions in Analysis: Filter out rows from one dataset based on another dataset’s content.


EXCEPT ( table1, table2 )
ParameterTypeDescription
table1TableThe primary table to compare. Rows from this table that are not in table2 will be returned.
table2TableThe secondary table used for comparison. Rows matching those in table1 are excluded.

How Does EXCEPT Dax Works

  1. Row Comparison: The function compares each row in table1 with rows in table2.

  2. Set Difference: Rows in table1 that do not have an exact match in table2 are included in the result.

  3. Distinct Rows: The comparison operates at the row level and considers all columns.

Key Points

  • All columns in the input tables are used for comparison.

  • The result table inherits the structure of table1.

What Does It Return?

  • Table: A table containing rows from table1 that do not appear in table2.

When Should We Use It?

  • Data Discrepancy Analysis: Identify mismatched or missing data between two datasets.

  • Filtering Data: Exclude specific rows from a dataset based on another dataset.

  • Database Comparisons: Compare results of queries from different database tables.

Examples

Basic Usage :

Find rows in one table that are not in another:


EXCEPT ( Products_A, Products_B )

 Result: Returns rows in the Products_A table that are not present in the Products_B table.

Column Usage

Apply the EXCEPT function to a filtered dataset:


EXCEPT (
FILTER ( Sales, Sales[Region] = "East" ),
FILTER ( Sales, Sales[Region] = "West" )
)

Result: Rows for sales in the “East” region that are not present in the “West” region.

Advanced Usage

Combine with other DAX functions for refined results:


SUMMARIZE (
EXCEPT (
CALCULATETABLE ( Orders, Orders[Status] = "Pending" ),
CALCULATETABLE ( Orders, Orders[Status] = "Completed" )
),
Orders[CustomerID],
"Pending Count", COUNTROWS ( Orders )
)

Result: A summary of customers with pending orders that are not marked as completed.

Tips and Tricks

  • Ensure the column structure and data types in both tables are identical; otherwise, the function will throw an error.

  • Use pre-filtering with functions like FILTER or CALCULATETABLE to narrow the datasets before applying EXCEPT.

  • Column Mismatches: If the tables do not have identical columns, the function cannot execute.

  • Performance: Comparing large tables can be computationally intensive. Optimize by reducing table size or filtering before comparison.

Performance Impact of EXCEPT DAX Function:

  • Optimize Comparisons: Use indexes or filters to limit the size of tables before applying EXCEPT.

  • Pre-Aggregate Data: Reduce data granularity when comparing large tables for faster results.

Related Functions You Might Need

FunctionDescription
INTERSECTReturns rows common to both tables.
UNIONCombines rows from two tables, removing duplicates.
EXISTSChecks if a specific row exists in a dataset.
FILTERFilters a table based on a given condition.

Want to Learn More?
For more information, check out the official Microsoft documentation for EXCEPT You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What does the EXCEPT function do in Power BI?

It returns rows from the first table that do not exist in the second table, effectively performing a set difference operation.

2. Can I use the EXCEPT function with tables of different column structures?

No, both tables must have identical column structures for the function to work.

3. Is the EXCEPT function case-sensitive?

Yes, row comparison is case-sensitive and considers all columns.

4. Can EXCEPT handle blank rows?

Yes, blank rows are treated as unique rows and included in the result if applicable.

5. How does EXCEPT differ from INTERSECT in Power BI?

EXCEPT returns rows exclusive to the first table, while INTERSECT returns rows common to both tables.

Previous article
Dax Function: DISTINCT (table)
Next article
Dax Function: FILTERS