Apply DAX calculation to all levels of a map hierarchy

[Keyser_Söze]

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

[MFelix]

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

[Keyser_Söze]

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