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 for race, so if I filter for a specific race that race will show as my output.  any ideas on how i can acheive this?

Total Associates (PME) =
var _gender=SELECTEDVALUE(Gender[Gender])
var _poc = SELECTEDVALUE(PoC[PoC (USA Only)])
var _value=
SUMX(FILTER('Talent Snapshot',[Gender]=_gender&&[Associate ID]<>""), 'Talent Snapshot'[Count Total Associates (PME)] )
var _total= if(ISFILTERED(PoC[PoC (USA Only)]),[Sum USA PoC Headcount],sum([Count Total Associates (PME)]))
var _all= if(ISFILTERED(PoC[PoC (USA Only)]),[Sum USA headcount],sum([Count Total Associates (PME)]))
var _percent=
DIVIDE(_value,_total)
var _totalpercent=
DIVIDE(_total,_all)
var _result=
IF(_value=BLANK(),0,_percent)
var _result1= IF(_value=BLANK(),0,_value)
return
IF(ISFILTERED(Gender[Gender])||ISFILTERED(PoC[PoC (USA Only)]),FIXED(_result1,0),FIXED( _total,0))
Need Help

13 Replies

  • 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

  • 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!

    • 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