Data not show unless Always Fully Expanded and another question
Hi, we are trying to design a report to compare resource demand and capacity from two different data source. In the attached example file, you can see that two tables have common dimensions but also have its own unique dimensions. For records that have same combination of Resource, ResourceType, Year, and Mth, I used ApplyMap to add the missing unique dimensions to data from the other table, and concatenate both tables at the end. By doing it, I identified two issues and would like to have your help.
1. The pivot table won't show any data unless Always Fully Expanded option is selected. However, I'd like to have a regular pivot table that I can collapse and expand dimensions. Can this be fixed?
2. In some cases, resource is allocated to projects for longer period than their capacity. You can see that John is allocated to projects for 12 months, but his capacity is only until June. We'd like to apply the following rule to the data but I don't know how:
- If value is null() of a month, get a value from the most recent month that has a value. For example, Location of December in Demand row is null, logic should check the previous months and get location information of NY from June sine June is the closest month to December that has a value.
I think you need to fix the all missing values for your Resource, time, location key. I think ApplyMAP usually not the right stragtegy to do this, because its a key, value replacement, doesn't provide anything else other than substituting values.
I try to solve your problem, in the attached file.