Using Filter with Dynamic Axis

[fibaek]

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!

[fibaek]

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

[v-zhenbw-msft · Accepted 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.