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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Keyser_Söze
Regular Visitor

Apply DAX calculation to all levels of a map hierarchy

Hi all,

 

I have been working on this issue for some time now but can't get my head around how to make it work. 

 

I have a map of the UK which shows local spend: the larger the spend the bigger the bubble (stating the obvious!). In visualizations under Location, there is a hierarchy made up of area (higher level) and location (more granular): the user can view the local spend by area and then next move to the local spend by location. So for each area there can be multiple locations.

 

What I'm trying to achieve is to display the local spend by area with the DAX expression below. But I also want to show the users - when they move to the next level (location) - the same local spend but this time filtered by location instead of area.

 

However, my DAX expression is not working as intended. I've tried it with a simple IF statement without the inner IF but that didn't work either.

 

My hierarchy is named "Derived.MapHierarchy" but it is not available when I started typing the DAX expression (not suggested by intellisense)

 

Can anyone help please?

 

Derived.MapLocalSpend% = IF( ISFILTERED( 'Project Datasheet'[Derived.MapArea] ),
                                                  DIVIDE( [Derived.MapLocalSpendArea], [Derived.MapAllSpend] ),
                                                  IF( ISFILTERED('Project Datasheet'[Derived.MapLocation] ),
                                                  DIVIDE( [Derived.MapLocalSpendLocation], [Derived.MapAllSpend] ),
                                                  BLANK() )
                                                 )

 

 

 

Local_Spend_Map.png

 

 

 

2 REPLIES 2
MFelix
Super User
Super User

Hi @Keyser_Söze,

 

On Power BI the calculations are based on the columns of your data, and when you create a Hierarchy you are no creating new columns just a "virtual" hierarchization so you use it on your visuals faster instead of having always to add the same columns over and over again from visual to visual.

 

To what I can see you have 3 measures:

  • Location Spend
  • Area Spend
  • All Spend

I don't know the full detail of your measures and your data but when using measures these are calculated based on context you can try to rewrite you measure like this:

 

 

Derived.MapLocalSpend% =
IF (
    HASONEVALUE ( 'Project Datasheet'[Derived.MapLocation] ),
    DIVIDE ( [Derived.MapLocalSpendLocation], [Derived.MapAllSpend] ),
    DIVIDE ( [Derived.MapLocalSpendArea], [Derived.MapAllSpend] )
)

I'm assuming that since you have an hierarchy one area as several locations, so what I'm checking is if there is only a single value selected on the MapLocation if yes then makes the location calculation otherwise make the area.

 

If this doesn't work can you share some data and expect results, so I can simulate and give you a bettwe answer.

 

Regards,

MFelix

 

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi MFelix,

 

First of all, thank you for your help with this. Can you send me your email address so that I can send you a copy of the visualisation please? It will be a a cut-down version of what I have and only contain the necessary data to make it work (the data is also non-sense data by the way).

 

Thank you,

Keyser_Söze

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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