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!  I have two slicers, both are an unconnected table as the source of the slicer (one is for gender and one is for race/ethnicity).  I have a measure with the gender slicer that is working correctly (see below).  I want to add an if/or statement into the measure for my other slicer (race/ethnicity), so if i select the race/ethnicity, it will filter for race/ethnicity.  is it possible to add an if/or statement to var _value below?

 

Associates with Potential Assessment = var _gender=SELECTEDVALUE(Gender[Gender])
var _value=
SUMX(
FILTER('Talent Snapshot',[Gender]=_gender&&[Potential]<>""),
[Count Total Associates (PME)])
var _total=SUMX(FILTER('Talent Snapshot',[Potential]<>""),[Count Total Associates (PME)])
var _all=
SUM([Count Total Associates (PME)])
var _percent=
DIVIDE(_value,_total)
var _totalpercent=
DIVIDE(_total,_all)
var _result=
IF(_value=BLANK(),0,_percent)
var _result1=
ROUND(_result,2)*100&"%"&" "&"("&IF(_value=BLANK(),0,FIXED(_value,0))&")"
return
IF(ISFILTERED(Gender[Gender]),_result1,
ROUND(_totalpercent,2)*100 &"%"&" "&"("&FIXED(_total,0)&")")
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi mmills2018,

I modify your formula to simplify the expressions and add additional filters to the 'CTA' calculation, you can try to use the following measure if it helps:

 

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, FIXED ( IF ( _flagG, cta_add, cta_raw ), 0 ) )
RETURN
    _round & "% (" & _fixed & ")"

 

Regards,

Xiaoxin Sheng