Count Multiple Rows from Multiple Table (Power BI)

Question

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?

Jon · Accepted Answer · 2021-05-10 17:01:04Z

1

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

edited May 10, 2021 at 17:01

answered May 9, 2021 at 21:02

Jon

4,9953 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mkRabbani · Accepted Answer · 2021-05-10 12:24:32Z

1

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())
    )
)

answered May 10, 2021 at 12:24

mkRabbani

16.9k2 gold badges17 silver badges26 bronze badges

Collectives™ on Stack Overflow

Count Multiple Rows from Multiple Table (Power BI)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related