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?
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi mmills2018,

Sorry for the later response.

This issue often appears when you try to compare two fields with different data types. (when you used to extract current value, the max function can be used with text fields)

Please check the fields that used in the max function can confirm they are numeric values. (I already modify the expression to add value convert part to the variable)

 

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,
            [Gender]
                IN VALUES ( Gender[Gender] )
                    && 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