Forum Discussion

bcdobbs's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago
Solved

Arbitrary Shaped Sets

Hi, I've been discussing evaluation contexts particularly with regard to arbitrary shaped sets with Strom on this thread: Evaluation context - mixed filter and row context    The original questio...
  • marcorusso's avatar
    marcorusso
    4 years ago

    The description we provide in the book applies to MDX queries and to other scenarios like those obtained using the context transition in a filter context managed by SUMMARIZE and/or ADDCOLUMNS.

    Power BI uses a function (SUMMARIZECOLUMNS) that introduces another behavior (could it be called a bug on top of another bug?) which generates results that are less clear and predictable. We never described in detail what happened, because the important thing is that you should use KEEPFILTERS around the table function in the iterator. Or iterate over a single column.

marcorusso's avatar
marcorusso
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

The description we provide in the book applies to MDX queries and to other scenarios like those obtained using the context transition in a filter context managed by SUMMARIZE and/or ADDCOLUMNS.

Power BI uses a function (SUMMARIZECOLUMNS) that introduces another behavior (could it be called a bug on top of another bug?) which generates results that are less clear and predictable. We never described in detail what happened, because the important thing is that you should use KEEPFILTERS around the table function in the iterator. Or iterate over a single column.

  • Strom's avatar
    Strom
    Frequent Visitor
    4 years ago

    Thank you all (Benjamin, Marco, Alexis) for your time. The goal of my original question was not to find out how to get the right result. I know that. The goal was to understand how the evaluation of that query works. 

    It would be great to find out more details about this behavior.

    • Zx2000's avatar
      Zx2000
      Icon for Advocate I rankAdvocate I
      3 years ago

      Same here, I spent 3 days tryging to figure out what is happening on the subsume level but the logic for  totals does not match for subtotals.
      As far as I understand  (which is different from your idea) is that the filter context for 2019 matrix row is 2019, 11 and 2019, 12 therefore Values(year) should iterate only for 2019 (as this is the only year from context coming from VALUES(year), isn't it? Why do you expect to have all months there as filter context  for this row is 11 and 12 only ?
      Due to context transition (iteration over only 2019) - I would expect to 2019 become a new filter which should chenge the original one for this column (which is the same - 2019) -> therefore the subsume should be calculated in a correct way(2019, 11 and 2019 12). 
      But the result for the 2019 subsume is year = 2019, 2020 and month = 11,12

      (2019/11, 2019/12, 2020/11, 2020,12)

  • AlexisOlson's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    It definitely does seem buggy.

     

    If you rewrite with CONCATENATEX instead of SUMX, then the Year subtotals are different but the Total matches:

     

    Using CONCATENATEX further as a debugger, we can see where Total comes from:

     

    Can you think of a reason why SUMX and CONCATENATEX behave differently for the year subtotals?

     

    the important thing is that you should use KEEPFILTERS around the table function in the iterator. Or iterate over a single column.

    Can you expand on this or link to something to read? Does VALUES ( 'Calendar'[Date] ) not count as a single column since it requires KEEPFILTERS in this case?

    • marcorusso's avatar
      marcorusso
      Icon for Most Valuable Professional rankMost Valuable Professional
      4 years ago

      It is probably because of the way SUM/SUMX is optimized for the subtotal, something that CONCATENATEX is not.

      If you look at the storage engine queries, you should realize that there are two problems:

      1. The way the storage engine requests are composed (which is consistent with the behavior expected with SUMMARIZE/ADDCOLUMNS)
      2. The different behavior produced by the formula engine trying to "reuse" part of the data already computed to reduce the need for additional storage engine requests.

      I didn't examine the CONCATENATEX query plan in detail, but I guess it does not have to generate SE queries for the intermediate totals. 

      I'm not sure what you mean in the last question - can you elaborate?

       

      Does VALUES ( 'Calendar'[Date] ) not count as a single column since it requires KEEPFILTERS in this case?

       

       

      • bcdobbs's avatar
        bcdobbs
        Icon for Community Champion rankCommunity Champion
        4 years ago

        I think AlexisOlson is referring to the end of your first reply:

         

        "We never described in detail what happened, because the important thing is that you should use KEEPFILTERS around the table function in the iterator. Or iterate over a single column."

         

        In the Sum Year measure it is iterating over a single column. 

        I assumed you'd actually meant, apply the filter with a single column eg Month-Year to avoid the arbitrary shapes set all together.

  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Thanks marcorusso really appreciate you taking time to reply! I hadn't considered that it was an interaction with the SUMMARIZECOLUMNS from the matrix that was causing the issue! Realise the use of KEEPFILTERS or using a more descriptive single column to form the filter with is the way forward; this was more of an academic exercise in understanding.

     

    For those interested I copied the DAX that the matrix forms and rewrote it using SUMMARISE/ADDCOLUMNS in DAX Studio connected to the demo file:

     

    DEFINE

    VAR FilterTable =
        TREATAS (
            {
                ( 2019, 11 ),
                ( 2019, 12 ),
                ( 2020, 1 ),
                ( 2020, 2 ),
                ( 2020, 3 )
            },
            'Calendar'[Year],
            'Calendar'[Month]
        )
        
    VAR ResultTable =
        CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZE (
                    Sales,
                    'Calendar'[Year]
                ),
                "Sum Year", 'Sales'[Sum Year]
            ),
            FilterTable
        )

EVALUATE

ResultTable

     This does indeed produce the expected "wrong" results:


    I thought I'd made peace with this in my head until I saw AlexisOlson response below! (loving the use of COCATENATEX for debugging!)