Forum Discussion
Using Filter with Dynamic Axis
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!
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.
2 Replies
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
