Iterators in Coalesced Mode

Using iterators within SUMMARIZECOLUMNS could face bugs and inconsistent behaviors when the semantic model has Coalesced setting for Value Filter Behavior.

In the failing example, there are several iterators: SUMX, CONCATENATEX, and COUNTX. Regardless of the use of VALUES (like in the example) or DISTINCT to get the list of values, the result is not consistent with the expected behavior, returning 2 or 1 depending on the expression used in the iterator.

Failing example:

DEFINE
    MEASURE Sales[SalesX] =
        SUMX ( VALUES ( Product[Color] ), DIVIDE ( [Sales Amount], [Sales Amount] ) )
    MEASURE Sales[SalesX1] =
        SUMX ( VALUES ( Product[Color] ), 1 )
    MEASURE Sales[COUNTX] =
        COUNTX ( VALUES ( Product[Color] ), [Sales Amount] )
    MEASURE Sales[CONCATENATEX] =
        CONCATENATEX ( VALUES ( Product[Color] ), Product[Color], "," )
    MEASURE Sales[COUNTROWS] =
        COUNTROWS ( VALUES ( Product[Color] ) )
    MEASURE Sales[SalesX Keep] =
        SUMX ( KEEPFILTERS ( VALUES ( Product[Color] ) ), [Sales Amount] )
    VAR YearsAndColor =
        TREATAS (
            { ( "CY 2008", "Red" ), ( "CY 2007", "White" ) },
            'Date'[Calendar Year],
            'Product'[Color]
        )

EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Color],
    YearsAndColor,
    "Sales Amount", [Sales Amount],
    "SalesX", [SalesX],
    "SalesX1", [SalesX1],
    "COUNTX", [COUNTX],
    "COUNTROWS", [COUNTROWS],
    "CONCATENATEX", [CONCATENATEX],
    "SalesX Keep", [SalesX Keep]
)
ORDER BY [Sales Amount] DESC

The result of several measurescan be 2 or 1, depending on the expression used in the iterator.

Example visible here: Iterators in coalesced mode - DAX.do

Workarounds:

1. Use the Independent setting for Value Filter Behavior. This is the recommended solution.

2. Avoid using the filter arguments in SUMMARIZECOLUMNS and move filters in an outer CALCULATETABLE function (not possible for DAX queries generated by Power BI).

   EVALUATE
   CALCULATETABLE ( 
    SUMMARIZECOLUMNS (
        'Product'[Color],
        "Sales Amount", [Sales Amount],
        "SalesX", [SalesX],
        "SalesX1", [SalesX1],
        "COUNTX", [COUNTX],
        "COUNTROWS", [COUNTROWS],
        "CONCATENATEX", [CONCATENATEX],
        "SalesX Keep", [SalesX Keep]
    ),
    YearsAndColor
   )
   ORDER BY [Sales Amount] DESC