Forum Discussion

George1970's avatar
George1970
Frequent Visitor
1 year ago

filtering virtual tables

I'm creating a virtual table called 'records' in a calculated column.  In one row it looks like this

virtual table

In this row DAX for the calculated column, the expression

 

 

COUNTROWS(
    FILTER(records, [Category] = "Other")
)

 

 

Returns a value of 5, which is correct (there are 5 rows where the Category value is Other).
 
My question is why does the alternative expression below not return 5 rows, BUT 9 instead?  
 

 

            COUNTROWS(
                CALCULATETABLE(
                    records,
                    FILTER(records, [Category] = "Other")
                )
            )

 

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi George1970

    The difference in the results between the two DAX expressions is due to the context in which the CALCULATETABLE function operates. Here’s a breakdown:

    First Expression:

    COUNTROWS(
    FILTER(records, [Category] = "Other")
)

    This expression directly filters the records table to include only rows where [Category] = "Other".
    It then counts the number of rows in this filtered table, which correctly returns 5.

    Second Expression:

    COUNTROWS(
    CALCULATETABLE(
        records,
        FILTER(records, [Category] = "Other")
    )
)


    CALCULATETABLE modifies the context in which the table is evaluated.
    The Filter function inside CALCULATETABLE is applied to the entire records table, but CALCULATETABLE can introduce additional context that might affect the result.
    If there are any existing filters or row contexts applied to records before this calculation,CALCULATETABLE might be including those contexts, leading to a different number of rows being counted.

    To ensure both expressions return the same result, you can use the first approach or ensure that CALCULATETABLE is used in a context where no additional filters are affecting the result. 






    Best Regards,

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

    • George1970's avatar
      George1970
      Frequent Visitor
      1 year ago

      Thanks Jayley.  I don't understand your point however.  Doesn't 

      FILTER(records, [Category] = "Other")

      modify the filter context of CALCULATETABLE so that CALCULATETABLE returns a table of 5 rows, which are then counted by COUNTROWS?