CODEWITHSUNDEEP
countpowerbidaxpowerquerypowerbi-desktop
Istiak Mahmood
Istiak Mahmood

Reputation: 2422

Count Multiple Rows from Multiple Table (Power BI)

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?

Upvotes: 0

Views: 1825

Answers (2)

Jon
Jon

Reputation: 4967

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

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

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

Upvotes: 1

Related Questions