Forum Discussion

DanielaZaickova's avatar
DanielaZaickova
New Member
5 years ago
Solved

"Special" advanced filtering

Hello, is it posible to prepare a special filter - I have a column chart with the 27 countries and their values. I want to see in this visualisation - e.g France AND Germany (this two must be always selected) AND  3rd country would be selected optional by user.

How can I "lock" selection of the France and Germany and prepared selection pane with other 25 countries?? 

Thanks a lot. 

 

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    DanielaZaickova 

    Here is one way of doing this. In the following example I want to keep both "Bulgaria" and Germany" visible always, and select other countries dynamically.

    1) Create an unrelated table to use as the slicer for your visual with the unique country names, excluding the values you wish to include in the visual permanently. Let's call this table Filter Table.

    This is the sample model for this example:

     

    And the filter table created in Power Query (NB: the values -ie Bulgaria and Germany - to be shown by default are filtered out from the table):

     2) Create a measure to filter the rows based on the selection on the slicer.

     

     

     

    Axis Filter = 
VAR SetC = { "Bulgaria", "Germany" }
VAR Countries =
    VALUES('Filter Table'[Filter Country])
    
VAR REF =
    UNION ( Setc, Countries )
VAR AxisC =
    VALUES ('Dim Country'[Country] )
RETURN
    COUNTROWS ( INTERSECT ( REF, AxisC ) )

     

     

     

    3) Finally, create your report page with visuals using the Dim Country [Country] as the axis, add the measure, and add the [Axis filter] measure by selecting the visual, and adding the [Axis Filter] to "Filters for this visuals" in the filter pane setting the value to 1. Add the Filter Table slicer.

    Format the slicer as you need (single select if this is what you need)

     

    I've attached the sample PBIX file for your reference.

8 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    If you only ever need to compare one country against France and Germany then you can create three measures

     

    - selected country value

    - France value

    - Germany value

     

    and then add all three measures to your column chart.

     

    But if you need to compare an arbitrary number of countries to France and Germany then that approach does not work.

    In that case consider the following:

    - separate the data into two tables, one table TableA for the France and Germany values, the other TableB for the other values

    - create a slicer based on TableB[Country]

    - create a calculated table Table = UNION(TableA,TableB)

    - create a measure 

    Measure = 
var f = union(filters(TableB[Country]),VALUES(TableA[Country]))
return CALCULATE(sum('Table'[Value]),INTERSECT(VALUES('Table'[Country]),f) )

    - Add the measure and the Table[Country] column to your visual

     

     

      • DanielaZaickova's avatar
        DanielaZaickova
        New Member
        5 years ago

        Hi, I followed your instructions, but I can see all eu countries

        I need to see only Bulgaria, Eu average and 3.selected country 

        Did I do anything wrong?

        Thanks

        Daniela

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    5 years ago

    DanielaZaickova 

    Here is one way of doing this. In the following example I want to keep both "Bulgaria" and Germany" visible always, and select other countries dynamically.

    1) Create an unrelated table to use as the slicer for your visual with the unique country names, excluding the values you wish to include in the visual permanently. Let's call this table Filter Table.

    This is the sample model for this example:

     

    And the filter table created in Power Query (NB: the values -ie Bulgaria and Germany - to be shown by default are filtered out from the table):

     2) Create a measure to filter the rows based on the selection on the slicer.

     

     

     

    Axis Filter = 
VAR SetC = { "Bulgaria", "Germany" }
VAR Countries =
    VALUES('Filter Table'[Filter Country])
    
VAR REF =
    UNION ( Setc, Countries )
VAR AxisC =
    VALUES ('Dim Country'[Country] )
RETURN
    COUNTROWS ( INTERSECT ( REF, AxisC ) )

     

     

     

    3) Finally, create your report page with visuals using the Dim Country [Country] as the axis, add the measure, and add the [Axis filter] measure by selecting the visual, and adding the [Axis Filter] to "Filters for this visuals" in the filter pane setting the value to 1. Add the Filter Table slicer.

    Format the slicer as you need (single select if this is what you need)

     

    I've attached the sample PBIX file for your reference.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi DanielaZaickova 
    I would create a new table as below

    And add two new measures to it as

     

    Country = 
VAR _country = SELECTEDVALUE('Country'[country_name])

RETURN SWITCH(SELECTEDVALUE(testTable[ID]),
1, "France",
2, "Germany",
3, _country)
    Value = 
Var _selected = SELECTEDVALUE('Country'[Country_name])
VAR _france = CALCULATE(COUNT('Country'[Country_name]),'Country'[Country_name] = "France")
VAR _germany = CALCULATE(COUNT('Country'[Country_name]),'Country'[Country_name] = "Germany")
Var _other = CALCULATE(COUNT('Country'[Country_name]),'Country'[Country_name] = _selected)

RETURN SWITCH(SELECTEDVALUE(testTable[ID]),
1, _france,
2, _germany,
3, _other)

     

    You can replace COUNT('Country'[Country_name]) with the required value as per your need.

    This will result in the values we need and only the third leg will change as per the selection.