Forum Discussion

mmills2018's avatar
mmills2018
Helper IV
5 years ago

Using If statement for a slicer?

Hello, I am using the below measure, I am trying to add to this measure.  I have two slicers, one that filters for gender and one that filters for race.  I want to add into this measure the slicer f...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

HI mmills2018,

You can try to modify the gender part and pack it into the if statement:

Associates with Potential Assessment =
VAR filtered =
    //table with public filter conditions
    FILTER (
        'Talent Snapshot',
        [Potential] <> ""
    )
VAR cta_add =
    //cta with additional filter
    SUMX (
        FILTER (
            filtered,
            IF (
                ISFILTERED ( Gender[Gender] ),
                [Gender] IN VALUES ( Gender[Gender] ),
                TRUE ()
            )
                && IF (
                    ISFILTERED ( Race[race/ethnicity] ),
                    [Race] IN VALUES ( Race[race/ethnicity] ),
                    TRUE ()
                )
        ),
        [Count Total Associates (PME)]
    )
VAR cta_raw =
    //cta wiht raw filter
    SUMX ( filtered, [Count Total Associates (PME)] )
VAR _flagG =
    //filter flag gender
    ISFILTERED ( Gender[Gender] )
VAR _round =
    ROUND (
        IF (
            _flagG,
            MAX ( 0, DIVIDE ( cta_add, cta_raw ) ),
            DIVIDE ( cta_raw, SUM ( [Count Total Associates (PME)] ) )
        ),
        2
    ) * 100
VAR _fixed =
    MAX ( 0, VALUE ( FIXED ( IF ( _flagG, cta_add, cta_raw ), 0 ) ) )
RETURN
    _round & "% (" & _fixed & ")"

Regards,

Xiaoxin Sheng

mmills2018's avatar
mmills2018
Helper IV
5 years ago

Thanks, but the slicer is only filtering for POC when I filter on Gender.  for example, if I have Female selected the filter for POC will then work.  If i do not have female or male filtered the POC filter does not work.  any ideas why this is?

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HI mmills2018,

    I think this should be related to the '_round' part.

    According to the original conditions, the 'gender' filter is a 'switch' that used to change calculations expressions based on selection.

    IF (
    _flagG,
    MAX ( 0, DIVIDE ( cta_add, cta_raw ) ),
    DIVIDE ( cta_raw, SUM ( [Count Total Associates (PME)] ) )
)

    Formula comment:

    if the filter flag equal to true, it gets the result of divide of 'cta_add' and 'cta_raw', in other cases it will get result divide the 'cta_raw' and aggregated result(based on current row contents).

    How did you handle the condition if the gender filter is optional?
    Regards,

    Xiaoxin Sheng