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

Question

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.

I DAX you may not need the calculated column. Just build a measure that counts the table2 column. When that measure is applied to a pivot table or visual that groups by the unique values, you automatically get the count per unique value. — teylyn
– teylyn, Commented May 25, 2017 at 20:38

David · Accepted Answer · 2017-05-25 20:47:54Z

31

If the tables are related, this is very simple:

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

Here is an example:

Your Table2 contains multiple rows per Table1 key:

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

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

answered May 25, 2017 at 20:47

David

3,4223 gold badges39 silver badges47 bronze badges

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

2 Comments

viejoEngineer Over a year ago

I'm running into a similar issue, but I need to look for the value in the same table/column. Also the value is not quite the same. For example, I have a workflow of 1, but multiple workflows can be created based on that workflow like 1-1, 1-2, 1-3, etc. Basically I need to count how many workflows were created based on each workflow. The workflows are stored in the same table/column. Any help on how to proceed would be greatly appreciated.

David Over a year ago

@JVM, that's not enough information to give you a precise answer. Please post it as a new question and include a minimal, complete and verifiable example.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related