Basic Question about Calculate - looking for an explanation

[jdusek92]

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 · Accepted solution]

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