Forum Discussion

iToune's avatar
iToune
Frequent Visitor
4 years ago
Solved

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

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

Need Help
  • iToune's avatar
    iToune
    4 years ago

    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.

5 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    iToune  can you try this

    Formula2 = CALCULATE(COUNTROWS('Table2'),FILTER('Table2','Table2'[vin]=max(Table1[VIN Num])))+0
  • iToune's avatar
    iToune
    Frequent Visitor
    4 years ago

    Hi,

     

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

    I really don't understand what is going on.

  • iToune's avatar
    iToune
    Frequent Visitor
    4 years ago

    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's avatar
      v-xiaotang
      Community Support
      4 years ago

      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.