Forum Discussion

H3nning's avatar
H3nning
Helper V
8 months ago
Solved

Filter not Filtering

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

 

KatAKatBCountValueDepartment
1X1100A
1Y1200A
0X1150B
0Y1100B
0Y-1-100B
1Y-1-50A

 

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!
Need Help
  • danextian's avatar
    danextian
    8 months ago

    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. 

     

5 Replies

  • danextian's avatar
    danextian
    Super User
    8 months ago

    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. 

     

  • H3nning's avatar
    H3nning
    Helper V
    8 months ago

    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! 

  • Rufyda's avatar
    Rufyda
    Super User
    8 months ago

    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 

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    8 months ago

    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.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    7 months ago

    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.