OR or UNION of multiple slicers

[joshhansen29]

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?

[Yanant1020]

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

[achinm45]

Hi @joshhansen29 ,

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

[joshhansen29]

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]

Hi @joshhansen29

Agree with your viewpoint on going with option 2.

Have you managed the implementation of  functionality ?

BR,

Achin

[joshhansen29]

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]

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.

[OwenAuger]

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.

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

[austinsense]

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