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

Thanks for this, quick question, i keep getting the error: "Function 'MAX' does not support comparing values of type integer with values of type text.  consider using the VALUE or FORMAT function to convert one of the values." 

 

it has to do with this line:

VAR _fixed =
MAX ( 0, FIXED ( IF (_flagG, cta_add, cta_raw ), 0 ) )
 
any ideas how to fix?
mmills2018's avatar
mmills2018
Helper IV
5 years ago

Anonymous any chance you were able to look into this?

  • 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

      parry2k any chance you can help me out with the additional question i have?   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