Dynamic Hierarchy Title

[JoaoMonteiro]

Hi,

I want to create a dynamic chart title that changes accordingly with the hierarchy level were the user is.
There are 3 levels: Pole, Business Line, Functional Area

When I’m in the ‘Pole’ level the title should be like “You are at Pole level”; If I’m at ‘Functional Area’ should be ‘You are at Functional Area level’

I’ve already create a measure that works fine in a matrix, but do not work on a visual Card.
Table example

My measure is like this:

Dynamic title =
SWITCH(
TRUE(),
ISINSCOPE(‘Organization’[Functional Area]), “Functional Area”,
ISINSCOPE(‘Organization’[Business Line]), “Business Line”,
ISINSCOPE(‘Organization’[Pole]), “Pole” )

I appreciate all the help provided.
Thanks

[Greg]

@JoaoMonteiro

a measure that works fine in a matrix, but do not work on a visual Card.

Hi @JoaoMonteiro. When using the measure in a matrix you get the implicit context (therefore ISINSCOPE works), however, there is no context for a card to use. I’m not sure exactly what you’re trying for, so if you could upload a work-in-progress PBIX file and an Excel mock-up of your desired outcome, that’d help the forum members in their pursuit.
Greg

[BrianJ]

@JoaoMonteiro,

As usual, @Greg is spot on in his assessment. With there being no inherent context in a card visual, you will need to build that context using virtual tables. Here are a number of posts I’ve made on this very topic that hopefully should help you work through a solution.

If not, please do post a PBIX file for a more specific solution.

– Brian

Dax calculation to sum based on criteria

@jprlimey, It’s all about evaluation context. When you drop the measure in the table, the context over which it is evaluating is clear (Review No). However, in a card there is no evaluation context. In case with insufficient evaluation context, DAX will do it’s best to guess, which is why sometimes you get a right answer, sometimes a wrong answer and sometimes a blank, which is DAX’s equivalent of ¯\_(ツ)_/¯. Thus, for these cards (and for the total row of the table for the Lvl 5 measure), …

Using "All-in" Date Harvest Approach with Cards

I received the following interesting question regarding the Date Harvest Deep Dive video I did recently for the Enterprise DNA TV YouTube channel as part of the ongoing time intelligence series that @Melissa and I have been doing: [image] Billy is correct that the approach certainly is easier when you have an explicit evaluation context on date in the visual. However, for visuals that don’t contain this explicit evaluation context (e.g., cards, gauges, etc.), the approach absolutely still wor…

Preventing a measure from summing up in KPI card

@Ikay, Cards can be challenging, because unlike tables, matrixes or charts, they don’t provide any inherent evaluation context. Thus, in situations like this you need to build the proper evaluation context into your measure in the form of a virtual table against which your measure can be evaluated. This post explains the issue in depth with an example. The same approach should be directly applicable to your problem. I hope this is helpful. If you need additional specific guidance, please po…

PS – looking through these posts and seeing how frequently this question does come up, I’m going to put together a YouTube video on this issue – look for it to post later this month…

[JoaoMonteiro]

Hi,
Sorry for not being clear.
See the images as an example.
As you can see the Title is placed based on the concatenation of the name of the value placed in Values, in this case, ‘Total Sales’, and the hierarchical level where we are, in this case ‘Sales Territory’.
If you drill to the next hierarchical level, the title automatically changes to ‘Total Sales + by + State Province’ and that is OK and what I pretend.
However, if you try to customize the title on the format options, the automatic update of the Title disappears.
For example:
Instead of the title that appears by default “Total Sales by Sales Territory” I want only “Sales Territory” and when I drill down instead of “Sales Territory by State / Province” I only want to appear “State / Province”
But if I make this change in the ‘Title text’ (picture 4) then it will always repeat the same title regardless of the level of hierarchy where I find myself.

[BrianJ]

@JoaoMonteiro,

OK, thanks for the clarification. I now see the problem clearly. For some reason, when Microsoft added dynamic titles based on measures in April 2019, one thing that still seems not to work properly is this particular use case of changing title based on hierarchy level. I have combed through the Microsoft Community and YouTube and haven’t found a direct fix for this.

However… I do have a somewhat kludgy fix and I wanted to check whether that would be acceptable to you as a solution. There’s a way based on unpivoting the data to basically mimic selection of hierarchy level by slicer. We could then harvest the slicer choice and use that as the basis for the dynamic title. As I said, it’s a roundabout way to get to the result, and before I make the effort to get that to work, wanted to see if that would meet your requirement?

It really is a strange exception, since on the face of it I definitely would’ve expected your SWITCH/ISINSCOPE structure to work . Interesting problem…

• Brian

[JoaoMonteiro]

Hi @BrianJ

I was already developing a different solution than the one you suggested.
I created bookmarks for each of the hierarchy graphics and buttons Drill Up and Drill Down. The effect is what you can see in the image.
Thank you for your help.

[BrianJ]

@JoaoMonteiro,

Nice solution. Would you be able to share the PBIX, so others who might have the same question can see exactly how you solved this?

Thanks!

– Brian

[JoaoMonteiro]

Hi @BrianJ

The data that feeds the pbix is sensitive and cannot be shared in any way.
I already downloaded a sample pbix and I will replicate the solution.
Once it’s ready, I’ll share the pbix with you.
Regards
Joao Monteiro

[JoaoMonteiro]

Sales Demo.pbit (49.9 KB) Hi @BrianJ

As promised I send you a pbix that has the solution I found to overcome the Dynamic Titles limitation in the Power BI drill.
I hope you like it and that it will serve as a solution for many members of DNA Enterprise.
I take this opportunity to thank all that I have learned with you.
Thanks

[BrianJ]

@JoaoMonteiro,

Thank you so much for taking the time to put together a detailed example that we can all learn from.
However, when I open the file I get the following parameter prompt:

What value should I enter here that will allow the file to load properly?

Thanks!

• Brian

[JoaoMonteiro]

Hi @BrianJ
I wasn´t able to upload the file because exceeds the 4GB allowded, so I tried to reduce it by creating a Power BI template.
But it is easier if you download the .pbix file directly from my Google Drive.
Below is the link that will allow you to download it.
I´ll appreciate any comments
Thanks

Download .pbix file

[JoaoMonteiro]

Hi @BrianJ

It seems that this is also not possible.
Please indicate an email to which I can send the .pbix

Regards
Joao Monteiro

[Greg]

Hi @JoaoMonteiro. Can you create a smaller dataset that illustrates the issue? Perhaps that would help with the upload…
Greg

[JoaoMonteiro]

Hi @Greg

I´m sending the .pbix with a smaller dataset.

Regards
JoaoSales Demo.pbix (3.9 MB)

[BrianJ]

@JoaoMonteiro,

Thanks again. Really nice solution here. I especially like how you made the bookmarks “position aware”, so that they only show up, down or both up and down depending on where in the hierarchy you are.

I’m sure this will be a valuable reference/example for others with the same problem, which based on my research on the Microsoft Community, is a sizable number of people.

• Brian

[JoaoMonteiro]

Hi @BrianJ

It is very good to receive, and in the last year I have received a lot from you.
Now I am also pleased to be able to contribute.

Regards
Joao

[BrianJ]

@JoaoMonteiro,

Thanks – that’s great to hear. That desire to give back to this community is exactly what motivated me to begin posting solutions, so I very much look forward to seeing more of yours in the future as well.

• Brian

[EnterpriseDNA]

Hi @JoaoMonteiro , did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!