DAX Measure is not filtering correctly

[jannahds]

Hello!  I've got a filter area and summary demographics card on my dashboard, like this:

The % male, % female and % decline/blank/other are based on DAX measures as follows:

Male = Divide(
CALCULATE(
	COUNTA('Light Wheel Survey'[_uuid]),
	'Light Wheel Survey'[What is your sex?] = "Male" ),
    COUNTA('Light Wheel Survey'[_uuid]))

Female = Divide(
CALCULATE(
	COUNTA('Light Wheel Survey'[_uuid]),
	'Light Wheel Survey'[What is your sex?] = "Female"),
COUNTA('Light Wheel Survey'[_uuid]))

Decline/Blank/Other = 
    DIVIDE(
        CALCULATE(COUNTA('Light Wheel Survey'[_uuid]),'Light Wheel Survey'[What is your sex?] = "Other" || 'Light Wheel Survey'[What is your sex?] = "Prefer not to answer" || 'Light Wheel Survey'[What is your sex?] = ""),
        COUNTA('Light Wheel Survey'[_uuid])
    )

 All data comes from the same table 'Light Wheel Survey'.

If I filter the data using 'age category' - you can see the measures work like so:

HOWEVER, if I filter by sex, then the measures don't filter, like this:

So, in the data, out of 15 interviews, we have 9 female, 3 male, and 3 unknown/blank (total 15).

When I filter for 'male', for example, the # of interviews updates to 3 (correct), the % male is 100% (3 out of 3) (correct), the % female is 300% (9 out of 3) (unexpected/incorrect), and the blank/other/decline is 100% (3 out of 3) (unexpected/incorrect).

I would expect, because we've filtered the data by male, I want to see 0% female and 0% other/blank/decline.

I can see on the card visual that 'filters and slicers affecting this visual' is 'What is your sex? is 'Male'':

And I've got a fairly complicated data model (eg., the model isn't that comlicated, there's just a lot of tables).  However, all the data in the measures and the filters are all coming from the same table.  So I really am at a loss!

Any ideas what could be causing this strange behaviour?  I'm sure I'm overlooking something!

Thanks!

Janna

[HotChilli]

I knew this would be where I end up.  A checking service for AI-generated DAX code.

"Open the pod bay doors, Hal, and give me a DAX total that works in a visual"

"I'm sorry, HotChilli, I'm afraid I can't do that"

----

I can't argue with the DAX above but, personally, if I find myself writing DAX that looks a bit unusual/over-complex I start thinking - "Can I simplify this?". If I have to write a measure that works for specific purposes in specific visuals, it's usually a spider-sense tingle-moment for me.

I always try to keep my measures generic and control filters with slicers but sometimes you have to do what you have to do.

All the best and thanks for the feedback and the well-asked question.

[HotChilli · Accepted solution]

It might appear strange but it's correct behaviour.

When you write:

CALCULATE(
	COUNTA('Light Wheel Survey'[_uuid]),
	'Light Wheel Survey'[What is your sex?] = "Female")

it really means

CALCULATE(
	COUNTA('Light Wheel Survey'[_uuid]),
	FILTER(ALL('Light Wheel Survey'[What is your sex?] ) , 'Light Wheel Survey'[What is your sex?]  = "Female"))

so all the filters are removed from 'Light Wheel Survey'[What is your sex?] and then the 'female' filter is applied.
In the 2nd example (in your post), the value in the filter pane (Male) is removed.

This means the numerator in both examples above (in your post) will be 9.  9/12 = 75%   9/3 = 300%

[jannahds]

@HotChilli , this is the kind of explanation I was hoping I would get, thanks for diving into the detail there, I think I'm wrapping my head around it.  I will do a bit more playing to try to understand this more fully (for example, still trying to understand why it works using other columns of data in the filters, just not on sex).

However, my next question is...what is the measure I need to write to get this to work as I want it to work, for example, if this is my current measure:

Female = Divide(
CALCULATE(
	COUNTA('Light Wheel Survey'[_uuid]),
	'Light Wheel Survey'[What is your sex?] = "Female"),
COUNTA('Light Wheel Survey'[_uuid]))

How do I change the numerator calculation so that it only selects "female" from the remaining rows of filtered data, not going back to "ALL" rows?

Thank you!

[jannahds]

@HotChilli, I actually had tried solving this problem before posting here with ChatGPT help, but he/she just could not pick up what might be going wrong.  However, I fed your response/explanation into ChatGPT, plus my further follow-up question, and FINALLY the AI seemed to 'get it'.  

So, this is what ChatGPT recommended I do to modify the measures - essentially use the 'KEEPFILTERS' function:

Female = Divide(
CALCULATE(
	COUNTA('Light Wheel Survey'[_uuid]),
	KEEPFILTERS('Light Wheel Survey'[What is your sex?] = "Female")),
COUNTA('Light Wheel Survey'[_uuid]))

Male = Divide(
CALCULATE(
	COUNTA('Light Wheel Survey'[_uuid]),
	KEEPFILTERS('Light Wheel Survey'[What is your sex?] = "Male")),
    COUNTA('Light Wheel Survey'[_uuid]))

Decline/Blank/Other = DIVIDE(
    CALCULATE(
        COUNTA('Light Wheel Survey'[_uuid]),
        KEEPFILTERS(
            FILTER(
                'Light Wheel Survey',
                'Light Wheel Survey'[What is your sex?] = "Other" ||
                'Light Wheel Survey'[What is your sex?] = "Prefer not to answer" ||
                'Light Wheel Survey'[What is your sex?] = ""
            )
        )
    ),
    COUNTA('Light Wheel Survey'[_uuid])
)

I've tested this, and using my current setup, with multiple filters applied, it gives me the answer I 'want' to see.

However, I wanted to come back here to post to actually ask - in your opinion, do you think this is the best solution to the problem?  If yes, that's great.  If not, how else would you solve this?  

I also just want to say thank you again - this isn't something I understood about the 'CALCULATE' function before, so the time you took to explain this really does mean a lot to me.