Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DanielaZaickova
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
PaulDBrown
Community Champion
Community Champion

@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
PaulDBrown
Community Champion
Community Champion

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






pranit828
Community Champion
Community Champion

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

lbendlin
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.