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 wa...
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!

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!