Complex Hardcode with Dates and Teams

Questions & Answers
#1

Good morning Team. Would appreciate some assistance with this current problem I’m trying to work through. Essentially, I’m trying to hardcode multiple values for a report only measure that pulls from two tables. Time: in Rows and Teams: in Columns. In my horrible drawing conception. I know the report for the BLUE column and I know how to make the measure for the GREEN columns.

Hardcoding the data for the YELLOW column is where I’m lost.

Is there a specific CASE example I can use that would pull the Week Rollup and then when I have the specific team selected… display a specific value that was hardcoded?

The Case would be… When X Team is selected (In columns) display the various static values in this COLUMN that corresponds to a specific weekly range in the associated ROW.

Thanks in advance! Have a great day.

image

#2

Hi @rodjay5
You can create CASE WHEN construction in your measure for the combinations of your team value and week number. Here is an example where I used “Project team”:

CASE WHEN 
  [Project team].CurrentMember.Name ='Ghost'AND [Time].CurrentHierarchyMember.Name = 'W01, Dec 30 2019' 
  THEN 10
  WHEN 
    [Project team].CurrentMember.Name ='Ghost'AND [Time].CurrentHierarchyMember.Name = 'W02, Jan 06 2020' 
    THEN 15
  WHEN 
    [Project team].CurrentMember.Name ='Astronaut' AND [Time].CurrentHierarchyMember.Name = 'W01, Dec 30 2019' 
    THEN 20
  WHEN 
    [Project team].CurrentMember.Name ='Unicorn' AND [Time].CurrentHierarchyMember.Name = 'W01, Dec 30 2019' 
    THEN 30
  WHEN 
    [Project team].CurrentMember.Name ='Robot' AND [Time].CurrentHierarchyMember.Name = 'W01, Dec 30 2019'
    THEN 40
END

In the report:

image
image1604×608 77.1 KB

best,
Gerda // support@eazyBI.com