Forum Discussion

J-ace's avatar
J-ace
Frequent Visitor
3 years ago
Solved

Data modelling advice / filtering a table based on unselected filter measure

I am trying to discover the best way possible to approach a problem, the data looks something similar to table A:

 

 

I need to allow the user to filter out (deselect from select all) a country, from this I want to display Table B:

However, I actually need to filter out the categories that are not present in 'Spain', therefore returning something similar to Table C, the average totals for the categories that exist in Spain AND the other countries (if present), but excluding the spain totals:

 

One approach I have tried it to create an independent table: 'Distinct Country':

 

Distinct Country = SUMMARIZE('Table A','Table A'[Country],'Table A'[Category])

 

With an unselected country measure used to filter the table:

 

 Unselected Country Measure =

var _1 = except(All('Table A'[Country]), allselected('Table A'[Country]))

return

calculate(countrows('Distinct Country'), filter('Distinct Country', 'Distinct Country'[Country] in _1))
 
 Distinct Country Table With Measure Added:

 

 
This returns the filtered result, but I am struggling to filter the original table based on these results, any suggestions would be appreciated or alternative methods to achieving the same result
 
Many thanks in advance!

 

 

  • There is no way to "select all but one" elements in a slicer. 

3 Replies

  • There are multiple red flags here.  What is the desired behavior when both SPAIN and MEX are deselected?

    • J-ace's avatar
      J-ace
      Frequent Visitor

      Thanks for the reply, for now there is no requirement for multi-select, only single-select filters.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User

        There is no way to "select all but one" elements in a slicer.