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
mmills2018's avatar
mmills2018
Helper IV
5 years ago

Anonymous parry2k can i please get help with this question, i was able to get the above measure to work but where i am having issues with the below.  I am able to filter by gender and then by race, but i can only filter to race when gender is selected.  I would like to filter to race regardless of gender being selected.  Any ideas how i can get the filter for race to function without having to select a value for gender?

VAR cta_add =
//cta with additional filter
SUMX (
FILTER (
filtered,
[Gender]
IN VALUES ( Gender[Gender] )
&& IF (
ISFILTERED ( PoC ),
[Race/Ethnicity] IN VALUES ( PoC[PoC (USA Only)] ),
TRUE ()
)
),
[Count Total Associates (PME)]
)
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