Issue with two slicers for the same table

[hk106]

Hi,

I have a table, Orders, with columns for OrderType (has to be 1 or 2) and Total.

I've added two slicers.  One that filters by OrderType of 1, and another that filters by OrderType of 2.  Both of them use Total as their only value.

What I want is for a user to be able to use the first slicer to filter only the OrderType of 1 records between (as an example) $100 and $200, and the second slicer to filter only the OrderType of 2 records between $400 and $500. 

Unfortunately, this result is producing zero results.  It seems that the slicers are somehow getting in each other's way, and regardless of the OrderType, the only Totals that are considered are those that would be included in both slicer's range.  For example, if the first slicer is $100 to $500 and the second slicer is $300 to $700, I'll end up with all records with a Total between $300 and $500.

Is there a step I'm missing?  I appreciate the help!

[v-henryk-mstf · Accepted solution]

Hi @hk106 ,

According to your description, even if you use the Edit interaction function, you cannot achieve the results of two filters in the same table at the same time. This is because the visual image of the table presents the merged set of multiple filters instead of the intersection.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[YukiK]

I believe this behavior is expected. You can workaround this by creating 2 tables instead of 1 table, each has OrderType of 1 and the other has OrderType of 2.

And then set the visual interactions so that each slicer affects appropriate table accordingly. To change visual interactions, go to "Edit Interactions" under "View" tab.

Hope that helps.

[pbi_106]

Thanks for the response YukiK!

I'm having a bit of trouble visualizing this.  Are you saying there would be two separate Order tables in this case, one for each OrderType?  Can I bring the data of two separate tables together for a table visual?

[YukiK]

No. You'd have the table you origianally had, which I believe contain both OrderType1 and OrderType2.

To visualize what you want, you will place two table/matrix visuals, each has either OrderType1 or OrderType2 column. And set the visual interactions between these tables and slicers so that OrderType1 slicer will affect only the visual that contains OrderType1, and do the same for OrderType2.

I created a demo file here for you to download: PBI file 

Hope that makes sense.

[pbi_106]

Thanks for the example, YukiK, that does help me follow.

I'm still holding out hope that there's another solution that allows users to apply these slicer filters to the same table visual without getting in each other's way, but it's good to know, worst case scenario, that there is still another option.

[YukiK]

Glad to be some help! I believe you could separate OrderTypes columns in to separate tables and make them so that they don't have relationships between them. That way, you might be able to do what you want, however, that may or may not be an issue in other visuals for your analysis.