Using If statement for a slicer?

[mmills2018]

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))

[mmills2018]

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]

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

[mmills2018]

@Anonymous any chance you were able to look into this?

[mmills2018]

@Anonymous any update on this or should i resubmit this question?  thanks!

[mmills2018]

@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]

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

[mmills2018]

Thanks, but the slicer is only filtering for POC when I filter on Gender.  for example, if I have Female selected the filter for POC will then work.  If i do not have female or male filtered the POC filter does not work.  any ideas why this is?

[Anonymous]

HI @mmills2018,

I think this should be related to the '_round' part.

According to the original conditions, the 'gender' filter is a 'switch' that used to change calculations expressions based on selection.

IF (
    _flagG,
    MAX ( 0, DIVIDE ( cta_add, cta_raw ) ),
    DIVIDE ( cta_raw, SUM ( [Count Total Associates (PME)] ) )
)

Formula comment:

if the filter flag equal to true, it gets the result of divide of 'cta_add' and 'cta_raw', in other cases it will get result divide the 'cta_raw' and aggregated result(based on current row contents).

How did you handle the condition if the gender filter is optional?
Regards,

Xiaoxin Sheng

[mmills2018]

@parry2k any chance you can help me out with the additional question i have?   thanks!

[Anonymous]

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]

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]

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

[parry2k]

@mmills2018 it will be easier if you post sample data and expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490