Change Legend Value Based On Drill Level

[PocketProtector]

I am tracking projects' phases through time on a bar char, from design to construction.  I am counting how many projects are in each phase at a given point in time as a kind of resource utilization chart.

If the user is looking at a Day drill level, that's easy.  But when the user drills up to Month, a project could be in both phases in a month if, let's say, it changes from design to construction on the 15th.  At that level I would like the project to be counted only once as being "in between" rather than design or constuction... until the user drills back down to day.

The In Between legend value could never exist at the day level but would appear as soon as the user drilled up to a month or quarter.  Any suggestions appreciated!

[Anonymous]

---

@PocketProtector wrote:

I am tracking projects' phases through time on a bar char, from design to construction.  I am counting how many projects are in each phase at a given point in time as a kind of resource utilization chart.

If the user is looking at a Day drill level, that's easy.  But when the user drills up to Month, a project could be in both phases in a month if, let's say, it changes from design to construction on the 15th.  At that level I would like the project to be counted only once as being "in between" rather than design or constuction... until the user drills back down to day.

The In Between legend value could never exist at the day level but would appear as soon as the user drilled up to a month or quarter.  Any suggestions appreciated!

---

Well, you should approach this in a different way. You should have a dimension called something like "Project Phases" and in there you should store all the possible phases (together with your "in-between"). Then, you should create a measure that would for any given time frame return the number of projects that are in any of the phases stored in there. Then your problem disappears...

Proper model design is more important than mastery of DAX: https://www.youtube.com/watch?v=pvIVMEFQokE

Best

Darek

[Stachu]

Hi, can you add sample tables from your model with anonymised data?

As described here:

How to Get Your Question Answered Quickly 

here is some general info on hierarchies that might be helpful

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/