Using Slicers or Filter pane for OR based logic

DAX DAX Calculations
1

I’m trying to figure how to create a filter or slicer setup that will allow OR type filtering in a published PBI Report that uses Azure SSAS as the data source.

Example: Sex = ‘Male’ or Country = ‘Scotland’

So, from this table:

PersonID Sex Country
10 Male England
20 Female Scotland
30 Male Scotland
40 NK Scotland

A filter for Sex = ‘Male’ OR Country = ‘Scotland’ would return:

PersonID Sex Country
20 Female Scotland
30 Male Scotland
40 NK Scotland

Whereas the default behaviour for PBI using filters Sex = ‘Male’ , Country = ‘Scotland’ would return:

PersonID Sex Country
30 Male Scotland

The users do not have access to the SSAS design and (ideally) a solution should require as little DAX as possible (creation of a new column is not supported and I’ve tried creating a measure to concatenate Sex and Country but have hit a dead end).

Any suggestions - or confirmation that it’s not possible - greatly appreciated.

Thank you

Stephen

2

@stephen.carr,

Take a look at this video. I think the approach discussed will get you what you’re looking for.

  • Brian
3

Brian,

Thank you for your suggestion and the technique you show will be very useful elsewhere in our project.

I was hoping that there’d be some user-friendly feature of PBI that I’d somehow missed!

Thanks again

Stephen

4

@stephen.carr,

If you’re open to using custom visuals, check out OkViz’s SmartFilter Pro. It has and/or logical filtering built in.

Here’s a pretty extensive post I’ve written on it that should be directly relevant to your issue:

I hope this is helpful.

  • Brian
5

Hi @stephen.carr, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

6

Hi - information rom Brain was very interesting and will be useful for other purposes but did not solve our enquiry. Thank you

7

@stephen.carr

Stephen =
CALCULATE (
    [Total Sales],
    UNION (
        CROSSJOIN ( ALL ( Table[Sex] ), VALUES ( Table[Gender] ) ),
        CROSSJOIN ( VALUES ( Table[Gender] ), ALL ( Table[Sex] ) )
    )
)
4 Likes
8

Hi @stephen.carr, did the response provided by @AntrikshSharma, If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

9

Hi @stephen.carr, we’ve noticed that no response has been received from you since the 6th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

10

Hi @stephen.carr, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!