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 any update on this or should i resubmit this question?  thanks!

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

    • 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