INTERSECT

How to Use the Power BI DAX function INTERSECT

What is the DAX Function INTERSECT?

The INTERSECT function in Power BI is a DAX function that returns a table that contains the intersection of two input tables. In other words, it returns only the rows that are common to both tables. The INTERSECT function is different from the JOIN function, which returns all the rows from both tables, including the ones that are not common. The INTERSECT function is useful when you want to compare two tables and find the common rows between them.

How Does the INTERSECT Function Work?

The syntax of the INTERSECT function is as follows:

INTERSECT(Table1, Table2)

The function takes two arguments: Table1 and Table2. These arguments must be tables or expressions that return tables. The INTERSECT function returns a table that contains the rows that are common to both Table1 and Table2.

Here’s an example to illustrate how the INTERSECT function works. Let’s say you have two tables: Sales and Inventory. The Sales table contains the sales data for different products, and the Inventory table contains the inventory data for the same products. You want to find out which products are both in the Sales table and the Inventory table. Here’s how you can use the INTERSECT function to achieve this:

CommonProducts = INTERSECT(Sales, Inventory)

The CommonProducts table will contain only the rows that are common to both Sales and Inventory tables. If a product is in the Sales table but not in the Inventory table, or vice versa, it will not be included in the CommonProducts table.

How to Use the INTERSECT Function in Power BI

Now that you know what the INTERSECT function is and how it works, let’s discuss how you can use it in your data analysis tasks in Power BI.

Step 1: Load Your Data into Power BI

First, you need to load your data into Power BI. You can do this by selecting the Get Data option from the Home tab in Power BI Desktop. You can then select the type of data source you want to connect to and follow the prompts to load your data into Power BI.

Step 2: Create Your Tables

Once your data is loaded into Power BI, you need to create your tables. You can do this by selecting the Modeling tab and then selecting New Table. You can then enter the DAX formula that defines your table.

Step 3: Use the INTERSECT Function in Your DAX Formulas

Now that your tables are created, you can use the INTERSECT function in your DAX formulas to find the common rows between them. For example, if you have two tables called Sales and Inventory, and you want to find the products that are common to both tables, you can use the following DAX formula:

CommonProducts = INTERSECT(Sales, Inventory)

Step 4: Create Visualizations

Finally, you can create visualizations to display your data. You can use the visualizations pane to select the type of visualization you want to create and drag and drop your tables and fields onto the visualization.

The INTERSECT function is a powerful tool in Power BI that allows you to find the common rows between two tables. It is useful when you want to compare two tables and find the common rows between them. In this article, we discussed what the INTERSECT function is, how it works, and how you can use it in your data analysis tasks. By following the steps outlined in this article, you can use the INTERSECT function to perform complex data analysis tasks with ease.

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)