Count joined rows with DAX

Question

i'm trying to calculate indicators with POWER PIVOT using DAX. I'm trying to count the joined rows between two tables :

Imagine we have two tables :

One for sales :

ID   $   Agent
A321 14  Agent1
A122 89  Agent2
A432 45  Agent6
A999 32  Agent2

One with only the agent of my team :

Name   Agent
Jack   Agent1
Peter  Agent2
Bill   Agent3
Mell   Agent4

I just want to count the number of transaction made by my team, you can see that agent6 is not part of my team so 3 of the 4 transaction are made by my team.(Expected output)

I already made the relation in my model in Power Pivot between "Agent" of table 1 and "Agent" of Table 2

I tried several formulas with no success with create the adequat measure :

=COUNTROWS(INTERSECT('Table1';'table2'))

--> Tables are not the same so this is not the function i should use

=COUNTROWS(FILTER('table2';'table2'[Agent] <> BLANK ()))

--> it does not display the right output (more than it should because the relation with table 1 is not considered here)

=COUNTROWS(FILTER(ALL(table1);Table1[agent] = table2[Agent]))

--> Does not work ("can't determine a unique value for column Table1[agent]")

It seems pretty basic but i can't achieve to calculate this indicator... Maybe i don't think the right way. Any Help will be appreciated. Thanks for you attention.

Icy Icy · Accepted Answer · 2020-08-05T07:07:52

Finally this the right formula to use if you want to count rows of an intersection between two table with DAX :

=COUNTROWS(NATURALINNERJOIN(table1;table2))

Count joined rows with DAX

1 Answers