Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
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
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:
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
User | Count |
---|---|
47 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |