Forum Discussion

quikin5's avatar
quikin5
Frequent Visitor
5 years ago
Solved

Exclude a visual from a certain value in slicer

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.

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

4 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey quikin5 ,

     

    you have to handle that in the DAX measure.

    There you can check for that combination and if that happens you should return all values, otherwise just the combination the slicer has.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
  • quikin5's avatar
    quikin5
    Frequent Visitor
    5 years ago

    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. 

    • selimovd's avatar
      selimovd
      Most Valuable Professional
      5 years ago

      Hey quikin5 ,

       

      if you give me a few tables or a data set or an example, I can help you with the solution.

       

      Best regards

      Denis

    • v-kelly-msft's avatar
      v-kelly-msft
      Community Support
      5 years ago

      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!