locked
CALCULATE() with simple predicate causes dimension duplication in pivot RRS feed

  • Question

  • When I use CALCULATE( COUNTROWS( FactTable ), DimensionTable1[Dimension1] = "xxxx" ), I get the proper results in terms of the row count. If I drag Dimension1 onto the pivot table, all totals remain correct, but when I drill to Dimension1 level of detail, I see every value for Dimension1 repeated, including those which are not "xxxx", each with the appropriate count at that level of detail.

    If I use CALCULATE( COUNTROWS( FactTable ), FILTER( DimensionTable1, DimensionTable1[Dimension1] = "xxxx" ) ), I get the proper results and I do not see the improper values of Dimension1 displayed.

    Why is this?

    Monday, July 21, 2014 9:11 PM

Answers

  • The first formula is simply changing the filter context of COUNTROWS().  It is look at every row of FactTable and only counting the ones with "xxxx". 

    Normally if you just had:

    COUNTROWS(FactTable) 

    The measure would first look at the rows and columns of the pivot and filter results accordingly based on their values. When you add CALCULATE() and then just a dimension set to a constant as the parameter, you are telling the measure to "ignore" the filter values in the pivot for that dimension and return the result for the value I have defined in the measure. It still looks at every row in FactTable and therefore returns a results for everything in the dimension table it will just always be the result of the constant value you defined in the parameter.

    When you add the FILTER(), the measure no longer looks at the entire FactTable.  FILTER() creates a subset of the FactTable where the table only contains rows where the corresponding dimension value equals "xxxx".  So now, as far as the measure is concerned, anything not within the subset table created by FILTER() doesn't even exist. So it doesn't "know" that there are any values other than "xxxx" and therefore doesn't evaluate for those other values.

    Hope that helps a bit.  It's actually a fairly complex and somewhat abstract concept to try and explain briefly in a couple paragraphs.  I think that's the best I can do.  There are many people on here who can probably do it more eloquently than me.

    There is a lot out there in books and blogs about DAX filter context and row context if you want to dig deeper.  It is important to understand to write more advanced measures.  Understanding it on some level is really the key to a deeper understanding of DAX in general.

    • Proposed as answer by Michael Amadi Tuesday, July 22, 2014 11:59 AM
    • Marked as answer by greggyb Tuesday, July 22, 2014 2:04 PM
    Monday, July 21, 2014 10:12 PM
    Answerer
  • in addition to what has already been said I will try to phrase it a bit differently

    using CALCULATE(<expression>, Table[Column] = "xxx") overwrites the current filter on Table[Column] so if you have Table[Column] on rows, it will overwrite the value of the current row and use the value of "xxx" instead

    using CALCULATE(<expression>, FILTER(Table, Table[Column] = "xxx")) does not overwrite the current filter but extends it - think of it like an AND in SQL. for the row "xxx" this is fine as the current filter is "xxx" and also the calculation filters on "xxx" so you end up seeing a value. For the row "yyy" you do not see a value because your row is on "yyy" and your calculations further filters on "xxx" (in SQL: SELECT ... FROM Table WHERE Column='yyy' AND Column = 'xxx') which of course returns now rows/result

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, July 22, 2014 10:26 AM
    Answerer

All replies

  • The first formula is simply changing the filter context of COUNTROWS().  It is look at every row of FactTable and only counting the ones with "xxxx". 

    Normally if you just had:

    COUNTROWS(FactTable) 

    The measure would first look at the rows and columns of the pivot and filter results accordingly based on their values. When you add CALCULATE() and then just a dimension set to a constant as the parameter, you are telling the measure to "ignore" the filter values in the pivot for that dimension and return the result for the value I have defined in the measure. It still looks at every row in FactTable and therefore returns a results for everything in the dimension table it will just always be the result of the constant value you defined in the parameter.

    When you add the FILTER(), the measure no longer looks at the entire FactTable.  FILTER() creates a subset of the FactTable where the table only contains rows where the corresponding dimension value equals "xxxx".  So now, as far as the measure is concerned, anything not within the subset table created by FILTER() doesn't even exist. So it doesn't "know" that there are any values other than "xxxx" and therefore doesn't evaluate for those other values.

    Hope that helps a bit.  It's actually a fairly complex and somewhat abstract concept to try and explain briefly in a couple paragraphs.  I think that's the best I can do.  There are many people on here who can probably do it more eloquently than me.

    There is a lot out there in books and blogs about DAX filter context and row context if you want to dig deeper.  It is important to understand to write more advanced measures.  Understanding it on some level is really the key to a deeper understanding of DAX in general.

    • Proposed as answer by Michael Amadi Tuesday, July 22, 2014 11:59 AM
    • Marked as answer by greggyb Tuesday, July 22, 2014 2:04 PM
    Monday, July 21, 2014 10:12 PM
    Answerer
  • Greg, 

    To add to Mike's point, what you are doing with CALCULATE is managing evaluation context. Some of the best info on this subject would be in chapter 8 of the Excel 2013 - Building Data Models with PowerPivot book, by Marco Russo and Alberto Ferrari.  

    Hope this helps

    Reeves


    Denver, CO

    • Proposed as answer by Michael Amadi Tuesday, July 22, 2014 11:59 AM
    Monday, July 21, 2014 10:36 PM
  • in addition to what has already been said I will try to phrase it a bit differently

    using CALCULATE(<expression>, Table[Column] = "xxx") overwrites the current filter on Table[Column] so if you have Table[Column] on rows, it will overwrite the value of the current row and use the value of "xxx" instead

    using CALCULATE(<expression>, FILTER(Table, Table[Column] = "xxx")) does not overwrite the current filter but extends it - think of it like an AND in SQL. for the row "xxx" this is fine as the current filter is "xxx" and also the calculation filters on "xxx" so you end up seeing a value. For the row "yyy" you do not see a value because your row is on "yyy" and your calculations further filters on "xxx" (in SQL: SELECT ... FROM Table WHERE Column='yyy' AND Column = 'xxx') which of course returns now rows/result

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    Tuesday, July 22, 2014 10:26 AM
    Answerer
  • Thanks very much for your explanations.

    I had a fairly good understanding of what the filter argument was doing, and you have confirmed it, but I didn't realize how different CALCULATE(<expression>, condition) was from CALCULATE(<expression>, FILTER()). I definitely have some more reading to do.

    Tuesday, July 22, 2014 2:08 PM