CODEWITHSUNDEEP
countpowerbidax
Bad_Mama_Jama
Bad_Mama_Jama

Reputation: 196

Power BI, DAX--How do I count rows in one table based on values in another table?

I have two tables, lets call them Table1 and Table2. Table1 has a column of unique values, Table2 has a column with the same values but repeated.

What I am trying to accomplish is to calculate the number of times that value appears in Table2 as a new column in Table1.

Upvotes: 12

Views: 96844

Answers (1)

David
David

Reputation: 3422

If the tables are related, this is very simple:

Number of Table2 rows = COUNTROWS(RELATEDTABLE(Table2))

Here is an example:
Relationship between Table1 and Table2

Your Table2 contains multiple rows per Table1 key:
Table2

Then you can add a Calculated Column to Table1 which counts the times each item appears in Table2:
Table1 with the calculated column

If the tables are not related, you can use CALCULATE and FILTER:

Number of Table2 rows =
CALCULATE(
    COUNTROWS(Table2),
    FILTER(
        Table2,
        Table2[Column1] = Table1[Column1]
    )
)

Upvotes: 31

Related Questions