Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdusek92
Helper IV
Helper IV

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:

jdusek92_0-1620251307888.png

 

Now I want to calculate the percentage of Female:

jdusek92_1-1620251387026.png

This works fine when no filter is applied:

jdusek92_2-1620251427425.png

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

jdusek92_3-1620251529664.pngjdusek92_4-1620251549044.png

 

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

jdusek92_5-1620251652292.pngjdusek92_6-1620251671454.png

 

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

 

 

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

1 REPLY 1
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors