Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi
I have used Guy In a Cube's guide to build a dynamic axis on a visual. However, I am unable to apply a filter to that visual. Example: I have a geographical hierarchy with region and country.
Region | Country |
Europe North | Germany |
Europe North | Holland |
Europe South | France |
Europe South | Spain |
Europe East | Poland |
Europe East | Czech Republic |
I can easily switch between the region and country view on the visual, but when I apply a slicer for i.e. region with the three values it does not display the selected region. Presumably because there is no link between my un-pivoted dimension table and the real dimension table on region level. Ideally I would select a a region in the slicer and the visual would only show the countries in that region. Is there any way to achieve this?
Bonus info: My unpivoted table has about 1.9mil. rows, so this solution is far from ideal. Data is imported from a HANA view.
Thanks!
Solved! Go to Solution.
Hi @fibaek ,
We can add a new region table and change your measure to meet your requirement.
1. Create a region table that has no relationship.
Region table = VALUES('Table'[Region])
2. We can create a measure like this,
Measure =
var _selectregion = SELECTEDVALUE('Region table'[Region])
return
if(
HASONEVALUE('Union Table'[Type]),
SWITCH(
VALUES('Union Table'[Type])
,"Country",
IF(
_selectregion="",
CALCULATE(SUM('Table'[Value])
,TREATAS(VALUES('Union Table'[Country])
,'Table'[Country])),
CALCULATE(SUM('Table'[Value]),TREATAS(VALUES('Union Table'[Country])
,'Table'[Country]),'Table'[Region]=_selectregion))
,"Region",CALCULATE(SUM('Table'[Value])
,TREATAS(VALUES('Union Table'[Country])
,'Table'[Region]))
)
)
3. If you are not familiar with the previous operation, please refer to this blog.
https://www.kasperonbi.com/dynamically-switching-axis-on-visuals-with-power-bi/
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
Hi,
Thanks a lot! It's working fully as intended. Only small thing was the ability to use 'Table'[Region] in a slicer as well, but I solved that with a relationship between 'Table' and 'Region Table'. Seems to be working even though my region is: Region Table = DISTINCT(VALUES('Table'[Region])).
Your help is highly appreciated.
/Sune
Hi @fibaek ,
We can add a new region table and change your measure to meet your requirement.
1. Create a region table that has no relationship.
Region table = VALUES('Table'[Region])
2. We can create a measure like this,
Measure =
var _selectregion = SELECTEDVALUE('Region table'[Region])
return
if(
HASONEVALUE('Union Table'[Type]),
SWITCH(
VALUES('Union Table'[Type])
,"Country",
IF(
_selectregion="",
CALCULATE(SUM('Table'[Value])
,TREATAS(VALUES('Union Table'[Country])
,'Table'[Country])),
CALCULATE(SUM('Table'[Value]),TREATAS(VALUES('Union Table'[Country])
,'Table'[Country]),'Table'[Region]=_selectregion))
,"Region",CALCULATE(SUM('Table'[Value])
,TREATAS(VALUES('Union Table'[Country])
,'Table'[Region]))
)
)
3. If you are not familiar with the previous operation, please refer to this blog.
https://www.kasperonbi.com/dynamically-switching-axis-on-visuals-with-power-bi/
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
57 | |
38 | |
36 |
User | Count |
---|---|
81 | |
67 | |
62 | |
46 | |
45 |