Count Multiple Rows from Multiple Table (Power BI)

0
votes

I am trying to write a Power BI query that can calculate the number of ROWS with a Condition.

Now I have 5 tables- Table1, Table 2, Table 3, Table 4, Table 5. Now in those Tables, I have two Column Called ID & Date. I would like to Count all ID where the Date is `not Empty

I am trying this Query but it is not helping my cause.

All Total Hires = 
SUMX(
UNION(
    SELECTCOLUMNS(Table1,"A",Table1[Name]),
    SELECTCOLUMNS(Table2,"A",Table2[Name]),
    SELECTCOLUMNS(Table3,"A",Table3[Name]),
    SELECTCOLUMNS(Table4,"A",Table4[Name]),
    SELECTCOLUMNS(Table5,"A",Table5[Name])
)
,IF([A] <> NULL, 1, 0))

Does Anyone know any solution to this Problem?

2
countpowerbidaxpowerquerypowerbi-desktop

2 Answers

1
votes

You can also try this below measure-

count_id = 
COUNTROWS(
    UNION(
        FILTER(Table_1, Table_1[date] <> BLANK()),
        FILTER(Table_2, Table_2[date] <> BLANK())
    )
)
1
votes

You can do something like this using COUNTROWS and BLANK(). Note: I've assumed that the Date is null/blank and not ' ' type of empty.

Table1 Non Blanks= CALCULATE(COUNTROWS('Table1'), FILTER('Table1', 'Table1'[Date] <> BLANK())

You create a measure per table, and add them together or

CALCULATE(COUNTROWS('Table1'), FILTER('Table1', 'Table1'[Date] <> BLANK())
+ CALCULATE(COUNTROWS('Table2'), FILTER('Table2', 'Table2'[Date] <> BLANK())
+ and Table3 etc