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

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.

Reply
fibaek
Helper I
Helper I

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!

 

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

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])

 

us1.jpg

 

us2.jpg

 

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]))
            )
)

 

us3.jpg

 

us4.jpg

 

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.

View solution in original post

2 REPLIES 2
fibaek
Helper I
Helper I

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
Community Support
Community Support

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])

 

us1.jpg

 

us2.jpg

 

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]))
            )
)

 

us3.jpg

 

us4.jpg

 

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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