Add OR to a Measure

[MouserMike]

I created a slicer along with a custom measure to allow me to filter a visual based on whether a particular field contained the value selected in the slicer. Below is the formual for the measure:

Impacted = 
IF (
    ISERROR (
        FIND (
            SELECTEDVALUE ( 'DepartmentStakeholders'[Department] ),
            SELECTEDVALUE ( Projects[ImpactedDepartments] )
        )
    ),
    0,
    1
)

How do I add an "OR" condition to also include projects with the same selected department in the Primary Department field?  I think it would be something like "OR SELECTEDVALUE = Projects[PrimaryDepartment]" but I'm not sure of the exact syntax.

[littlemojopuppy]

Try something like this...

Impacted = 
IF (
    ISERROR (
        FIND (
            (SELECTEDVALUE ( 'DepartmentStakeholders'[Department] ) || 
            SELECTEDVALUE ( 'Projects'[PrimaryDepartment] )) &&
            SELECTEDVALUE ( Projects[ImpactedDepartments] )
        )
    ),
    0,
    1
)

[MouserMike]

[littlemojopuppy]

Lose the double ampersand and replace with a comma

[MouserMike]

That removed the error, but now nothing is being returned when clicking a department in the slicer; results are always empty.

[littlemojopuppy]

So I was never quite sure where you wanted the OR placed or if I had the field name correct.  The syntax should work and you should be very close...

[MouserMike]

The fieldnames are correct. 

The "OR" should be between  'Projects'[PrimaryDepartment] and 'Projects'[ImpactedDepartments]).  If either of those fields contain the 

SELECTEDVALUE ('DepartmentStakeholders'[Department], the result of the FIND should be True.

[littlemojopuppy]

Did you try changing the measure appropriately?

[MouserMike]

I've updated the measure with each of your suggestions but am still getting the errors.

[littlemojopuppy]

A couple things...

Without your PBIX file, I really can't tell you what the solution is.  I'm giving you my best guess.  I gave you how to incorporate AND and OR into measures...you need to figure it out.

More importantly, I've been participating in Microsoft forums covering VB/VBA, TSQL and Power BI for about ten years (Google my name to see!).  I've asked questions - and at times very stupid ones - and I've answered questions.  One thing I've learned is that it is extremely bad form to ask someone to "solve it for me".  People who are volunteering their time, effort and thought to help you want to see you trying as well.  Best of luck!

[Anonymous · Accepted solution]

@MouserMike -

Try something like this:

Your Measure = 
var a = FIND(SELECTEDVALUE('DepartmentStakeholders'[Department]),SELECTEDVALUE('Projects'[PrimaryDepartment]),1,0)  
var b = FIND(SELECTEDVALUE('DepartmentStakeholders'[Department]),SELECTEDVALUE('Projects'[ImpactedDepartments]),1,0)  
return MIN(MAX(a,b),1)

Hope this helps,

Nathan

[MouserMike]

@Anonymous that's exactly what I was looking for. Thank you.

[MouserMike]

That is generating an error on the closed parenthesis after the last SELECTEDVALUE statement:

[littlemojopuppy]

The double pipe - || - is the OR operator and the double ampersand - && - is the AND operator.  Check the parenthesis that everything is properly closed...

[MouserMike]

I checked and double-checked and as far as I can tell, everything is closed.  I don't understand how the || and && fit into the formula, but it seems to behave like there is a missing argument.

[littlemojopuppy]

What is the error message?