Single select slicer

[Ania26]

Hello,
I have a single select slicer *Dim_country=Country.
On the stacked bar chart I want to show values for all countries which belong to the same Region as selected country.
For example: single select country=France , so Region Europe.

Than on the chart I want to see all countires in Europe with some value.

[cengizhanarslan]

Step 1) Create a disconnected selector table

No relationship to anything. Use this as your slicer instead of Dim_country[Country].

CountrySelector =
DISTINCT (
    SELECTCOLUMNS ( Dim_country, "Selected Country", Dim_country[Country] )
)

Step 2) Update the measure

Region Total =
VAR _SelectedCountry =
    SELECTEDVALUE ( CountrySelector[Selected Country] )
VAR _SelectedRegion =
    CALCULATE (
        SELECTEDVALUE ( Dim_country[Region] ),
        Dim_country[Country] = _SelectedCountry
    )
RETURN
    CALCULATE (
        [Your Measure],
        REMOVEFILTERS ( Dim_country[Country] ),
        Dim_country[Region] = _SelectedRegion
    )

[Ania26]

Hello, If I make another slicer for Country then I have two on one page. 

[MFelix]

Hi @Ania26 

Not sure if you have the value of the chart taking into consideration the region or the country. you may need to change the filter context of your calculations something similar to this:

Region Total =
  VAR RegionSelected = SELECTEDVALUE(Country[Region])
  Return
  CALCULATE(
             [Your Measure],
            REMOVEFILTER(Country[Country]),
            Country[Region] = RegionSelected
  )

Be aware that this is just a sample DAX that may need to be updated based on your model.

[Ania26]

Hello,
This does not work. I have tried many different measures and still cannot solve this.

[MFelix]

Hi @Ania26 ,

How do you have your visual setup, and also the country / region are they in the same table are they part of the a dimension table or a fact table?

I have made a small example has you can see belowe if I select a country the bar charts gets the total value of the region:

My measure is:

Total Sales Group = 
            VAR SalesGroup = SELECTEDVALUE('Sales Territory'[Group])
            Return
            CALCULATE(
                [Total Sales], 
                REMOVEFILTERS('Sales Territory'[Name]), 
                'Sales Territory'[Group] = SalesGroup)

Can you please give some more details so I can check what is the best approach.

[Ania26 · Accepted solution]

I have a table called *Dim_Country with only country names.
Then I have another table Global Data with all data including Region , Country name and values of total consumption.

On the page I have slicer, single select which selects one country for example France and shows all data related to France on every chart.
I want to show where France is in relation to other countries in the same region, Europe, based on the Consumption. SO I want to have all counties in Europe including France. This is the only chart that shows any other countries. 

[MFelix]

Hi @Ania26 ,

The problem that you have is related with the way you setup your chart,  since you want to show information at the country level and you are using the slicer on country level the visual will never show any other countries except the one that is selected.

To have this working you need to go for the solution that @cengizhanarslan  refer to have second table however in my opinion this table is not used for the select but for the axis on the chart that will allow you to get the correct value:

Total Sales Group = 
            VAR SalesGroup = SELECTEDVALUE('Sales Territory'[Group])
            Return
            CALCULATE(
                [Total Sales], 
                 'Sales Territory'[Name] = SELECTEDVALUE('Sales Territory Filter'[Name])
                 && SELECTEDVALUE('Sales Territory Filter'[Group]) = SalesGroup
                 )

Be aware that you may need to get the SalesGroup from the fact table,

In this case I have created a Sales Territory Filter that is a replica of the dimension I use for country.

The Slicer is gettign information from the dimension table and then on the bar chart I'm using the disconnected table to get the countries that way I keep a single slicer and that specific chart works has intended.

[Ania26]

Thank you for help.