Search code examples
powerbidaxpowerquerypowerbi-desktopm

Power BI count null rows

I have two columns. Both have a general key INT value called TicketId, but one table has less TicketIds than another table. Both tables have different quantity of columns, so =EXCEPT() formula wouldn't fit.

For example, the current counting of rows looks like this:

Dates Tab1 Tab2
08.11.2022 1058 950
09.11.2022 1058 1056
22.11.2022 2342 302

I am looking for a DAX measure forluma that will calculate TicketIds that exist in Tab1, but doesn't exist in Tab2. It should be not a formula like 1058 - 950 = 108. Because after there will be drillthrough to show all the TicketIds that were calculated (exist in Tab1 and doesn't exist in Tab2).

Dates Tab1 Tab2 Tabcross
08.11.2022 1058 950 108
09.11.2022 1058 1056 2
22.11.2022 2342 302 2040
Solution

  • Let's say you have a model like this:

    Modeling View

    and your datasets like this:

    Data

    Define your measures like this:

    OnlyTable1 = COUNTROWS(VALUES(table1[TicketId]))
OnlyTable2 = COUNTROWS(VALUES(table2[TicketId]))
ID_Difference = COUNTROWS(EXCEPT(
                    VALUES(table1[TicketId]),
                    VALUES(table2[TicketId])
))

    Then if we put it on a table visual:

    ViewWAY