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

HI mmills2018,

AFAIK, power bi use 'and' logic with 'filter' effect to interact with other visuals, if you want to achieve advanced filter effects(e.g. OR logic, all match, not match...), I'd like to suggest you use an unconnected table as the source of a slicer. Then you can use Dax expression to interact and compare with the selection to achieve additional filter effects.

If you confused about these, please share some more detailed information to help us clarify your scenario and test to coding formula.
Regards,
Xiaoxin Sheng

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