EXCEPT

How to Use the Power BI DAX function EXCEPT

Understanding the EXCEPT Function

The EXCEPT function in Power BI is used to filter tables based on a set of criteria. It returns a table that contains all the rows from the first table (table1) that are not present in the second table (table2). The EXCEPT function is similar to the MINUS operator in SQL.

The syntax for the EXCEPT function is as follows:

EXCEPT(table1,table2)

Where:

- **table1**: The first table to compare.

- **table2**: The second table to compare.

Using the EXCEPT Function in Power BI

To use the EXCEPT function in Power BI, you need to follow these steps:

1. Open Power BI Desktop and load the required data into the dataset.

2. Click on the "New Measure" button in the "Fields" pane.

3. Enter a name for the measure and type the following formula:

MeasureName = EXCEPT(table1,table2)

4. Replace "MeasureName" with the name you want to give the measure.

5. Replace "table1" and "table2" with the names of the tables you want to compare.

Once you have entered the formula, click on the "Check" button to validate the formula. If there are no errors, click on the "Save" button to save the measure.

Examples of Using the EXCEPT Function

Let's take a look at some examples of how to use the EXCEPT function in Power BI.

Example 1: Filtering Customers based on Orders

Suppose you have two tables in your dataset: "Customers" and "Orders". You want to filter the list of customers who have not placed any orders. You can use the EXCEPT function to achieve this.

Customers_No_Orders = EXCEPT(Customers, Orders)

This formula will return a table that contains all the customers who are not present in the "Orders" table.

Example 2: Filtering Products based on Sales

Suppose you have two tables in your dataset: "Products" and "Sales". You want to filter the list of products that have not been sold. You can use the EXCEPT function to achieve this.

Products_No_Sales = EXCEPT(Products, Sales)

This formula will return a table that contains all the products that are not present in the "Sales" table.

Example 3: Filtering Employees based on Departments

Suppose you have two tables in your dataset: "Employees" and "Departments". You want to filter the list of employees who are not part of any department. You can use the EXCEPT function to achieve this.

Employees_No_Departments = EXCEPT(Employees, Departments)

This formula will return a table that contains all the employees who are not present in the "Departments" table.

The EXCEPT function in Power BI is a powerful tool that can help you filter and analyze data in a more efficient way. By using the EXCEPT function, you can easily compare two tables and filter the data based on a set of criteria. We hope this article has given you a good understanding of how to use the EXCEPT function in Power BI.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)