If/or statement with slicers

[mmills2018]

Hello,

I have the below meaure in power bi,  for my "return" i want to set it up so if i to (PoC[PoC (USA Only)]) it will give me my PoC calc (cta_rawpoc) and if i filter to ( Gender[Gender] ) it will give me my gender calc (cta_raw).  any ideas how i can use an if/or statement with slicers?

test2 = 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] ) ), [Count Total Associates (PME)] )

VAR cta_poc =SUMX ( FILTER (filtered,[Race/Ethnicity] IN VALUES ( PoC[PoC (USA Only)] ) ), 'Talent Snapshot'[PME USA Headcount] )

VAR cta_raw =

//cta wiht raw filter

SUMX ( filtered, [Count Total Associates (PME)] )

VAR cta_rawpoc = SUMX(filtered, [Sum USA headcount])

VAR _flagG = ISFILTERED(Gender[Gender])

VAR _flagpoc = ISFILTERED(PoC[PoC (USA Only)])

VAR _round =

ROUND (

IF (

_flagG,

MAX ( 0, DIVIDE ( cta_add, cta_raw ) ),

DIVIDE ( cta_raw, SUM ( [Count Total Associates (PME)] ) )

),

2

) * 100

VAR _roundpoc = round(IF(_flagpoc, MAX ( 0, DIVIDE ( cta_poc, cta_rawpoc ) ),

DIVIDE ( cta_rawpoc, SUM ( 'Talent Snapshot'[PME USA Headcount] ) )),2)*100

VAR _fixed =

MAX ( 0, VALUE ( FIXED ( IF ( _flagG, cta_add, cta_raw ), 0 ) ) )

VAR _fixedpoc =

MAX ( 0, VALUE ( FIXED ( IF ( _flagpoc, cta_poc, cta_rawpoc ), 0 ) ) )

RETURN

[v-jayw-msft · Accepted solution]

Hi @mmills2018 ,

If I understand you correctly, you could use ISFILTERED() function with SWITCH() or IF() function.

For example:

SWITCH(TRUE(),ISFILTERED(slicer1),measure1,ISFILTERED(slicer2),measure2,measure3).

Measure = 
var _a = CALCULATE(DISTINCTCOUNT('Table'[Associate ID]),FILTER('Table','Table'[Company]=SELECTEDVALUE('Table'[Company])&&'Table'[Potential]<>BLANK()))
var _b = CALCULATE(DISTINCTCOUNT('Table'[Associate ID]),FILTER('Table','Table'[Company]=SELECTEDVALUE('Table'[Company])))
return
_a/_b

Measure 2 = 
var _a = CALCULATE(DISTINCTCOUNT('Table'[Associate ID]),FILTER('Table','Table'[Company]=SELECTEDVALUE('Table'[Company])&&'Table'[Potential]<>BLANK()))
var _b = CALCULATE(DISTINCTCOUNT('Table'[Associate ID]),FILTER(ALL('Table'),'Table'[Company]=SELECTEDVALUE('Table'[Company])&&'Table'[Potential]<>BLANK()))
return
_a/_b

The same for the POC slicer.

Best Regards,

Jay

[parry2k]

@mmills2018 see attached, tweak it as per your need.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[mmills2018]

thanks but my filters are not connected to the data source.  I need an If Poc is filter then XYZ, if gender is filter than XYZ

[mmills2018]

Hi @parry2k any ideas?

[mmills2018]

here is sample data:

Associate ID	Gender	Race/Ethnicity	Potential	Company
1	Female	White	Potential	A
2	Male	LatinX	Potential	A
3	Male	Black	Potential	A
4	Male	Asian	High Potential	A
5	Male	White	Grow in Role	A
6	Female	White		A
7	Male	Black	Potential	A
8	Female	Black	High Potential	A
9	Female	Latinx	Grow in Role	B
10	Female	Latinx		B
11	Female	Asian		B
12	Female	Asian	Potential	B
13	Female	Asian	Potential	B
14	Male	White	Grow in Role	B

and here is my expected output:

	Associates with Potential
Company A	87.5% (7)
Company B	66.7% (4)

when I filter by Gender and select Female, I want my "Associates with Potential" column to update based on gender values, see below:

Company A	28.6% (2)
Company B	75% (3)

when I filter by PoC and select Asian, Black or Latinx, I want my "Associates with Potential" column to update based on PoC values, below is all PoC but when I select "Asian" in my slicer I would like to see the values for Asian population: 

Company A	71.4% (5)
Company B	75% (3)

I have two slicers(POC with POC(USA Only Values) and Gender with Gender Values, both are not pulling from my data source (I can't for purposes with female and poc calcs).  My measure is set up correctly, i just don't know how to return female values when female is selected or poc value when asian is selected or both, asian females selected.  any ideas?

[mmills2018]

Hi @parry2k  - did the above help you at all?

[parry2k]

@mmills2018 it will be much easier if you provide sample data and expected output, it is very hard to do reverse engineering of such a long DAX expression without understanding the data and the requirement.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡