Solved: Exclude a visual from a certain value in slicer

quikin5 · ‎07-13-2021

Hi,

I have a slicer and a clustered bar chart affected by this slicer. The slicer affects the bar chart axis.

Suppose the slicer has three items (and thus the bar chart has three bars).

I want to exclude the bar chart from the slicer JUST for a certain item of the slicer. This is, when selecting this item in the slicer, the bar chart should look as if I were not selecting any item.

v-kelly-msft · ‎07-16-2021

Hi @quikin5 ,

Create a measure as below:

Measure = 
var _tab=EXCEPT(ALL('Table'),ALL('Table (2)'[State]))
Return
IF(MAX('Table (2)'[State]) in FILTERS('Table'[Name]),CALCULATE(SUM('Table (2)'[Score])),
IF(MAX('Table'[Name]) in _tab,CALCULATE(SUM('Table (2)'[Score]),FILTER(ALL('Table (2)'),'Table (2)'[State]=MAX('Table (2)'[State])))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

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

View solution in original post

quikin5 · ‎07-13-2021

@selimovd Thanks for your reply! Maybe a little example could help. Suppose I have two tables. Table 1 consists of 1 column with the name of U.S. states and "United States" (51 rows). Table 2 consists of three columns, the first is a student id, the second the state, and the third is a test score. Use Table 2 to graph the average test score by state on a bar chart and use Table 1 to create a slicer that filters by state. If you select "United States" in the slicer, then the bar chart would look blank (since Table 2 only contains states), and I don't want that to happen. Instead, what I want is that when I select "United States", the chart looks as if nothing is selected in the slicer.