cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

"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. 

DanielaZaickova_0-1611093607177.png

 

1 ACCEPTED SOLUTION
Super User II
Super User II

@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:

model.JPG

 

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):

Filter table.JPG

 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.

result.JPG

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

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

8 REPLIES 8
Super User II
Super User II

@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:

model.JPG

 

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):

Filter table.JPG

 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.

result.JPG

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

 

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Super User II
Super User II

Hi @DanielaZaickova 
I would create a new table as below

pranit828_0-1611113560599.png

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.

pranit828_1-1611113664324.png

 





PBI_SuperUser_Rank@1x.png
Hope it resolves your issue? 
Did I answer your question? Mark my post as a solution!

Appreciate your Kudos, Press the thumbs up button!!👍
Linkedin Profile

Hi, thanks for your help. I will try 😉

Daniela

Super User III
Super User III

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

 

lbendlin_0-1611110820293.png

 

screen1.jpg

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

Where does "Eu average" come from all of a sudden? That was not mentioned in the original post.

Hi, there is no calculation or figure behind the EU28 value. It is the value as same as other country. So there is no need to calculate any avarege numbers. 

There is the same goal - to have 2 countries selected  and to compare 3rd country to them.

Thanks

Daniela

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors