Filter not Filtering

[H3nning]

I have a workbook with a slicer and 3 measures in it. I cannot share the real data, but it looks like this:

KatA	KatB	Count	Value	Department
1	X	1	100	A
1	Y	1	200	A
0	X	1	150	B
0	Y	1	100	B
0	Y	-1	-100	B
1	Y	-1	-50	A

I have a switch build in to sum Count or Value dependend on users choosing. Also I have a slicer for department. 

My 3 measures:

Total:

SWITCH(VALUES('Switch'[KPI])

    ,"Count"

    ,CALCULATE(

        SUM('Table'[Count])

    )

    ,"Value"

    ,CALCULATE(

        SUM('Table'[Value])

    )

)

with that one everything is fine. Filtering on department also works out.

KatA:

SWITCH(VALUES('Switch'[KPI])

    ,"Count"

    ,CALCULATE(

        SUM('Table'[Count])

        ,'Table'[KatA]=1

    )

    ,"Value"

    ,CALCULATE(

        SUM('Table'[Value])

        ,'Table'[KatA]=1

    )

)

This also seems to be fine. The filter through department also changes values.

KatB:

SWITCH(VALUES('Table'[Switch])

    ,"Count"

    ,CALCULATE(

        SUM('Table'[Count])

        ,'Table'[KatB] = "X"

    )

    ,"Value"

    ,CALCULATE(

        SUM('Table'[Value])

        ,'Table'[KatB] = "X"

    )

)

For me, the measures KatA and KatB should work identically, but KatB seems to be not affected by the filter on department. It is always the same value, which makes no sense to me. Can anyone give me a hint what could be the problem here???

Thanks and best regards!

[V-yubandi-msft]

Hi @H3nning ,

May I know if your issue is resolved, or if you still need any additional details. Please let us know if we can help further.

Thanks.

[V-yubandi-msft]

Hi @H3nning ,

If you have the opportunity to review @danextian response, you'll see that it meets your requirements. Please review it and let us know if you need any additional assistance.

Thank you @danextian  for your helpful input.

[danextian · Accepted solution]

Hi @H3nning 

Why is KatB using a different switch table? Also, I would use SELECTEDVALUE instead of VALUES as the latter returns a table instead of a scalar value - no problem if there is only one selection but will return an error if not.  These aside, I can't replicate your issue. Your measure should work as intended. 

[Rufyda]

Hi @H3nning 

Use a numeric flag column or explicit boolean logic:

CALCULATE(
SUM('Table'[Value]),
'Table'[_KatB] = 1
)

For reliable slicer interaction, prefer numeric calculated columns or boolean flags over text comparisons inside measures.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!
Regards,
Rufyda Rahma | MIE 

[H3nning]

Ok, I found a workaround, but it makes no sense to me:

I created a calculated column _KatB= IF(Table[KatB] = "X",1,0).

And when I use this field in the measure instead of KatB directly it works out fine. KatA is also a calculated field to be honest. So does it have to do with the fact that it is calculated or that it is a number, that it works that way??? Can anyone explain please?

THX!