How does the DAX engine handle my measure?

[Mahar92]

=VAR varTableAddCol = 
    ADDCOLUMNS(
        qryCustomers,
        "Order Quantity",
        CALCULATE(CountRows(qryOrders))
    )

VAR varFilteredTable =
    Filter(
        varTableAddCol,
        [Order Quantity] >= 5
    )

Return

CALCULATE([Total Sales],varFilteredTable)

Hi!

I have a question about the way my measure is handled by the Power Pivot engine:

In the variable section of my measure (see screenshot below) I create a new, virtual table to which I add a column using AddColumns().

My question is whether this process of creating the virtual table is repeated for each pivot cell (so in my case

for Consumer, Corporate and Home Office)? Or is it only done once for all pivot cells? 

If it is done for each pivot cell, wouldn't it be more efficient to permanently add this new column as a calculated column to the source table? Else I imagine that the calculation times may be unreasonably high.

[edhans]

It is generating it for every row in qryCustomers where they are visible in the filter context. So if you have a list of Customer in your pivot, and qryCustomers is your DIM customer table, then it is only one one time per row, because the filter context limits it to just that customer.

You can shorten it to this though. No need for the addcolumns. You just want to iterate over the customer table like so (Table names are changed as I tested using my existing data)

Iterator = 
VAR varFilterTable =
    FILTER(
        'Customers',
        CALCULATE(COUNTROWS('Sales')) > 100
    )
RETURN
    CALCULATE(
        [Total Quantity],
        varFilterTable
    )

I tend to like using variables to build as I go, but this one is short enough you could just write it this way:

CALCULATE(
    [Total Quantity],
    FILTER(
        VALUES( 'Customers'[CustomerKey] ),
        CALCULATE(
            COUNTROWS( 'Sales' )
        ) > 100
    )
)

VALUES here just iterates over the customer key. Never filter a table, just columns. 😀

If this is large, several million records, this will perform better. KEEPFILTERS+FILTER+ALL is faster than FILTER+VALUES, but only on really large tables or very complex models.

CALCULATE(
    [Total Quantity],
    KEEPFILTERS(
        FILTER(
            ALL( 'Customers'[CustomerKey] ),
            CALCULATE(
                COUNTROWS( 'Sales' )
            ) > 100
        )
    )
)

By the way, 9 out of 10 times when I am doing this, I start with ADDCOLUMNS() so I can see what is going on as well, but once I have the logic down, I just iterate over my table. No need to actually add the column.