Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
Proud to be a Super User!
Paul on Linkedin.
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.
Proud to be a Super User!
Paul on Linkedin.
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.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |