cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
joshhansen29
Frequent Visitor

OR or UNION of multiple slicers

Hi All,

 

I'm trying to combine two slicers with an OR or UNION. I have two fields that I want to filter on without interfereing with each other. For example if I have a slicer for Employee and another for Channel. I want to be able to see all records where the Employee=A OR the Channel=1. Right now I have to pull a report for each filter, then combine the results. Which is a major pain in the pantalones! 

 

Any thoughts on how I might be able to do this?

 

One option i've read on forums for other platforms (Qlikview) would be to do a cross apply to create a table of every possible combination and then create a new slicer on the variation results, but I think that could get rediculously ugly especially where I want multiple selections of each field.

 

Another option suggested is to Pull the data in twice with an additional colum that pulled the Employee in the first pull and the Channel in the second pull then finish them off with a union. The result would be a single slicer that includes All Employee names and all Channels. In theory, it could work, but it would be messy to have to look through employee names and Channels in the same slicer and it would require additional management of aggregation to ensure aggregate values aren't inflated. (The bigger peeve is that I would only be able to filter to those two specific fields.)

 

Is there a better way of accomplishing an OR or Union with multiple slicers?

 

8 REPLIES 8
austinsense Impactful Individual
Impactful Individual

Re: OR or UNION of multiple slicers

I have a gut instinct that this is possible but I have no idea how to do it.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
achinm45 Advocate III
Advocate III

Re: OR or UNION of multiple slicers

Hi @joshhansen29 ,

What will be result when you select one employee from employee and nothing from Channel ?

 

joshhansen29
Frequent Visitor

Re: OR or UNION of multiple slicers

Hi Achinm45,

 

That is a great question! I hadn't thought about the fact that you would have to have at least 1 selection from each in to filter the results. I could see a few work arounds, assuming that it is possible to change the functionality of a slicer:

1. Additional logic that changes the OR to an AND when the slicer has no selections made.

2. The dashboard would require that at least 1 selection from both slicers be selected.

 

I think that option 1 would be ideal, but option 2 is probably more likely to feasable.

 

I can see so much potential in having a new "OR Slicer" visualization type, but i think option 2 would at least show proof of concept.

 

Thoughts? 

achinm45 Advocate III
Advocate III

Re: OR or UNION of multiple slicers

Hi @joshhansen29

Agree with your viewpoint on going with option 2.

Have you managed the implementation of  functionality ?

 

BR,

Achin

joshhansen29
Frequent Visitor

Re: OR or UNION of multiple slicers

Hi @achinm45,

 

Unfortunately no, I have no idea if it is even a possibility. I was hoping that I could get an expert to tell me if it is even possible to change the way a slicer functions. I'm hoping that someone knows of some advanced settings for slicers that I just haven't noticed yet.

 

Thanks!

austinsense Impactful Individual
Impactful Individual

Re: OR or UNION of multiple slicers

Expert Opinion - there's no setting on a slicer that will make this happen.

 

Expert Opinion - you can create a measure that will make this happen.

Austin is VP Operations at PowerPivotPro and a professional self-service enthusiast 🙂
OwenAuger Community Champion
Community Champion

Re: OR or UNION of multiple slicers

Here's an idea of how you might do this...

Dummy example uploaded here: PBIX file on dropbox 🙂
It's a bit like basket analysis (http://www.daxpatterns.com/basket-analysis/)

 

You can make a copy of each dimension you want to be part of the union, and relate to the fact table with an inactive relationship.

 

Union filters.png
Then you can then create measures to simulate the union using the UNION function, such as below.

Filters on the tables with active relationships (Employee & Channel) can be maintained on top of the 'union' filters (using KEEPFILTERS just in case there are complex filters).

Orders Union =
CALCULATE (
    COUNTROWS ( Sales ),
    KEEPFILTERS (
        CALCULATETABLE (
            UNION (
                CALCULATETABLE (
                    SUMMARIZE ( Sales, Employee[Employee], Channel[Channel] ),
                    USERELATIONSHIP ( Sales[Channel], UnionChannel[UnionChannel] )
                ),
                CALCULATETABLE (
                    SUMMARIZE ( Sales, Employee[Employee], Channel[Channel] ),
                    USERELATIONSHIP ( Sales[Employee], UnionEmployee[UnionEmployee] )
                )
            ),
            ALL ( Channel ),
            ALL ( Employee )
        )
    )
)

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Yanant1020 Advocate I
Advocate I

Re: OR or UNION of multiple slicers

Does anyone know if this is an official request yet so that it can be voted on?

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors