Power pivot - new calculated column

[bongielondy]

I have 2 tables in Power Pivot, Table 1 and Table 2. Table 1 has the Models. Table 2 has the Models and a new column I have created. This new column should count the number of Models in Table 1. The 2 tables are not related.

Table 1

Model Site

sierra cs 29 main street

727 optimum honeysuckle complex

sierra cs bayes store

727 optimum north shopping centre

727 optimum sheel garage



Table 2

Model Number

727 optimum 3

sierra cs 2

xylene 0


I have used the COUNTROWS and the CALCULATE(COUNTAX) functions but still get an error. What will be the correct formula for the column Number?

 

[mart37]

=CALCULATE(COUNTA(Table1[Model]), FILTER(Table1,Table1[Model]=Tablel2[Model]))

 

[bongielondy]

Thanks a lot Mart. The formula works!!!