Countrows(Filter) and Calculate(Countrows, Filter) not giving the same results

[iToune]

Hi all,

I am trying to add a calculated column to my Table1 to count the number of rows in the Table2 where Table2[vin] = Table1[VIN Num].

At first I wrote this formula:

Formula1 = COUNTROWS(FILTER('Table2','Table2'[vin]=Table1[VIN Num]))+0

This worked, but then I needed to add a second criteria so I tried a formula with  Calculate, thinking I would apply two filters. I first tried to just apply the first filter to test it, and wrote this:

Formula2 = CALCULATE(COUNTROWS('Table2'),FILTER('Table2','Table2'[vin]=Table1[VIN Num]))+0

From what I understand I should have the same results, but it's not the case. The calculate formula seems to give smaller numbers, but not all the time.

Any idea of what is happening ?

Regards

[iToune · Accepted solution]

OK,

so, it semms that the issue xas that there was a relationship (via a third table) between Table1 and Table2. I deleted the relationship and now have correct results.

If anyone has info on how calculate behaves with relationships in calculated column, I'd live to learn more because for now I don't get it.

[v-xiaotang]

Hi @iToune 

You can try this calculated column,

count = CALCULATE(COUNTROWS(Table2),FILTER(ALL(Table2),Table2[vin]=Table1[VIN Num]))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[iToune]

Hi,

Thanks @smpa01 for your reply, but it did not work...

I really don't understand what is going on.

[smpa01]

@iToune any chance you can provide sample data ?

[smpa01]

@iToune  can you try this

Formula2 = CALCULATE(COUNTROWS('Table2'),FILTER('Table2','Table2'[vin]=max(Table1[VIN Num])))+0