EXCEPT – comparison of tables – finding of additional rows (DAX – Power Pivot, Power BI)

This is about the EXCEPT function in DAX. This functions takes two tables, compares them and show the result, which is a table containing rows present in the first table and NOT present in the second table.

You can do something similar with Inner query connection in Power query or in DAX queries.

If you want to see the common rows (now different rows), use INTERSECT.

Let´s use these two tables. In one of them there are employees working in some company this month. In the second table there are employees working there last time. We need the list of new people – working in the company in this month only (the additional rows).

Let´s upload both tables to Power BI. Then create new table and use EXCEPT to compare them. The syntax is totally simple – just write the names of both tables.

The result table contains additional rows.

EXCEPT needs the same number of columns in both tables. It sees the rows identical if they have values in all columns identical.

4 Comments

  1. Buenos días,

    Funciona muy bien la función, pero como puedo encontrar las diferencias entre dos tablas, al realizar filtros directamente sobre el reporte, que la función sea dinámica?

  2. I have 2 tables with identical “Project Number” fields, but have another field of Amount in both the tables having different value for every row.
    Now I want projects which should be present in both the tables but Amount column should get the values from first tables only.
    Note: I want to do all this in the DAX measure directly so that I can get an aggregate value of Amount field from table 1 for those projects matches in both the tables.
    Can you please guide.

  4. The logic could be like this:
    =SUMX(Table,IF(
    value from first table)=(value from second table using LOOKUPVALUE, RELATED…);
    value from first table;
    0)
    This sumarizes values from first table, if there is the same amout in the second table for specified project number

Comments are closed.