Forum Discussion

jdusek92's avatar
jdusek92
Icon for Advocate III rankAdvocate III
5 years ago
Solved

Basic Question about Calculate - looking for an explanation

Hello,

I have come across a CALCULATE behaviour that I cannot quite understand, but I believe it is a basic think about how it works:

 

I have this dummy table:

 

Now I want to calculate the percentage of Female:

This works fine when no filter is applied:

But when I filter by Gender, it is giving me some unexcpected results:

 

But when I filter by GenderCopy (exact copy of Gender Column), I get the expected results:

 

Could someone please give me an easy to understand explanation why the value is different when filtering by the duplicate column? And why am I getting 43%?

 

Warm regards,

Jakub

 

 

 

  • OwenAuger's avatar
    OwenAuger
    5 years ago

    Hi jdusek92 

    The short explanation is that filter arguments in CALCULATE overwrite existing filters by default.

     

    Just restating your measure here for reference:

     

     

     

    Female% =
CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Gender] = "F" )
    / COUNTROWS ( 'Table' )

     

     

     

    In your example, the "numerator "of your Female% measure overwrites any existing filter on 'Table'[Gender] and replaces it with 'Table'[Gender] = "F".

     

    This default behaviour can be changed so that filter arugments instead intersect with existing filters, by wrapping them in KEEPFILTERS. I suspect that you may want to rewrite your measure as follows, rather than using GenderCopy:

     

     

     

    Female% =
CALCULATE ( COUNTROWS ( 'Table' ), KEEPFILTERS ( 'Table'[Gender] = "F" ) )
    / COUNTROWS ( 'Table' )

     

     

     

     

    To explain the results you were getting originally:

    1. No filters applied:
      • Numerator adds filter Gender = "F" (since no existing filter), so Numerator = 3
      • Denominator = 10
      • Result = 3/10 = 30%
    2. Filter Gender = "F" on slicer
      • Numerator replaces Gender = "F" with Gender = "F", i.e. no change, so Numerator = 3
      • Denominator is filtered by Gender = "F" (by slicer) so Denominator = 3
      • Result = 3/3 = 100%
    3. Filter Gender = "M" on slicer
      • Numerator replaces Gender = "M" with Gender = "F", so Numerator = 3
      • Denominator is filtered by Gender = "M" (by slicer) so Denominator = 7
      • Result = 3/7 = 43%
    4. Filter GenderCopy = "F" on slicer
      • Numerator adds Gender = "F" (since no existing filter on Gender), resulting in Gender = "F" & GenderCopy = "F", so Numerator = 3
      • Denominator is filtered by GenderCopy = "F" (by slicer), so Denominator = 3
      • Result = 3/3 = 100%
    5. Filter GenderCopy = "M" on slicer
      • Numerator adds Gender = "F" (since no existing filter on Gender), resulting in Gender = "F" & GenderCopy = "M", so Numerator = blank (since there are no rows in filter context)
      • Denominator is filtered by GenderCopy = "M" (by slicer), so Denominator = 7
      • Result = blank/7 = blank

     

    There are numerous articles on this topic out there, and these may be good ones to start with:

    https://www.sqlbi.com/articles/using-keepfilters-in-dax/ 

    https://www.sqlbi.com/articles/filter-arguments-in-calculate/ 

     

    Regards,

    Owen

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    5 years ago

    Hi jdusek92 

    The short explanation is that filter arguments in CALCULATE overwrite existing filters by default.

     

    Just restating your measure here for reference:

     

     

     

    Female% =
CALCULATE ( COUNTROWS ( 'Table' ), 'Table'[Gender] = "F" )
    / COUNTROWS ( 'Table' )

     

     

     

    In your example, the "numerator "of your Female% measure overwrites any existing filter on 'Table'[Gender] and replaces it with 'Table'[Gender] = "F".

     

    This default behaviour can be changed so that filter arugments instead intersect with existing filters, by wrapping them in KEEPFILTERS. I suspect that you may want to rewrite your measure as follows, rather than using GenderCopy:

     

     

     

    Female% =
CALCULATE ( COUNTROWS ( 'Table' ), KEEPFILTERS ( 'Table'[Gender] = "F" ) )
    / COUNTROWS ( 'Table' )

     

     

     

     

    To explain the results you were getting originally:

    1. No filters applied:
      • Numerator adds filter Gender = "F" (since no existing filter), so Numerator = 3
      • Denominator = 10
      • Result = 3/10 = 30%
    2. Filter Gender = "F" on slicer
      • Numerator replaces Gender = "F" with Gender = "F", i.e. no change, so Numerator = 3
      • Denominator is filtered by Gender = "F" (by slicer) so Denominator = 3
      • Result = 3/3 = 100%
    3. Filter Gender = "M" on slicer
      • Numerator replaces Gender = "M" with Gender = "F", so Numerator = 3
      • Denominator is filtered by Gender = "M" (by slicer) so Denominator = 7
      • Result = 3/7 = 43%
    4. Filter GenderCopy = "F" on slicer
      • Numerator adds Gender = "F" (since no existing filter on Gender), resulting in Gender = "F" & GenderCopy = "F", so Numerator = 3
      • Denominator is filtered by GenderCopy = "F" (by slicer), so Denominator = 3
      • Result = 3/3 = 100%
    5. Filter GenderCopy = "M" on slicer
      • Numerator adds Gender = "F" (since no existing filter on Gender), resulting in Gender = "F" & GenderCopy = "M", so Numerator = blank (since there are no rows in filter context)
      • Denominator is filtered by GenderCopy = "M" (by slicer), so Denominator = 7
      • Result = blank/7 = blank

     

    There are numerous articles on this topic out there, and these may be good ones to start with:

    https://www.sqlbi.com/articles/using-keepfilters-in-dax/ 

    https://www.sqlbi.com/articles/filter-arguments-in-calculate/ 

     

    Regards,

    Owen