Differentiate between filter from the same field used in a slicer and on a chart

[Anonymous]

Hi,

Please refer the screenshot. 

Scenario/Requirement: I have a page with (A) a simple stacked column chart showing sales over months & quarters, stacked by Product Category and (B) A slicer on same Product Category used in chart.

When a user scrolls anywhere on a column (i.e. any of the stacks), i want the tooltip to show Sales Amount for all Product Categories (i.e. all stacks) of that column.

My Problem: I created a tooltip page with a measure for doing this. I got stuck at a point where my measure is unable to distinguish between the filter coming from slicer and from the stack of the column. Both are Product Category from the same table. I want to use filter from Slicer one but ignore one from the stack of the column. 

All DAX functions i tried work on the dataset and Not the visual being used for filter. So, what happens is if my measure uses

REMOVEFILTER - Product Categories from both places get removed and vice versa. 

ISFILTERED - It does the same i.e. both get removed and vice versa

SELECTEDVALUE - I thought this only identified only external filters i.e. slicers in my case but it seems to identify one on column as well. 

Please feel free to suggest any alternative way to resolve this or in my approach - How can i differentiate a filter context coming from the slicer and coming from the stack.

Thanks.

[parry2k]

@Anonymous share your file using one drive/google drive.

[Anonymous]

Hi @parry2k - I had sent it to your linkedIn provided in your signature around the same time of the messages exchanged above but oops!, didn't tell you here. Please see if it helped. Thanks.

[parry2k]

@Anonymous Good you solved it but having a duplicate column should have worked, I probably test on my end, it is all about the measure to get this work.

[Anonymous]

Sure, if you'd like my file to help as a headstart, let me know how to share with you ... Please also do share your final solution.

[Anonymous · Accepted solution]

Hi @parry2k , As an extension of your idea --- Although adding a duplicated Product Category column didn't solve my problem because it seemed to continue to work as if in unison with the Original one (Or maybe i couldn't make it work through formulas) ... but if i create the duplicated Product Category column into a different table (create a separate dimension as a Reference Query), the two Product Category columns behave independently and solved my problem ... It's obviously not an ideal solution but it works.

In an ideal situation, ofcourse i should have been able to control the same column through it's separate contexts over separate visuals independently .... and the next possible best was to atleast be able to use a copy within the same table !! 

[parry2k]

@Anonymous you need to create a duplicate column for your product category and use this duplicate column on the stack bar and from there now you can control in measure what to do. Since right now you are using the same column for both stacked bar and slicer, I don't think there is a way to solve this.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

Hi @parry2k 

Thanks for your response. I tried it but didn't work to show all Product Categories in the tooltip. Below is my measure and I have attached screenshots from report page and tooltip page.

Here's the measure am using: 

Sales Amount (Ignore Product Category From Stack) =

var retValue = CALCULATE(SUM(fctSales[SalesAmount]),FILTER(ALLSELECTED(fctSales), 1=1), REMOVEFILTERS(dimProduct[Product Category (Duplicate)]))

return (retValue)

Here's the screen from Report Page showing slicer selection, tooltip (from measure above):

Tooltip Page using Matrix, Product Category (Of slicer) as Row Header & Sales Measure as shown