Hi,
I have two separate csv files.
file 1 has data about the project info (project number, project name, type, scale all dimensions and “row count” as measure) Each project has one type, one scale)
file 2 has data about plans with resources in terms of projects (“project number”, “project name”, “resourceid” as dimensions, “planned” as measure).
I upload those two files.
When I want to see the “planned” (measure in file 2) for each “Project number” I can see it.
But when I try to group these data with a dimension from file 1 (i.e. “Scale”) I can not see the data. (Figure) (To see the planned hours distribution in terms of the project scale)
When I switch the measure from “planned” (in file 2) to “row count”
(in file 1) I can see the data.
Any comments ?
Thank you.
Hi @LA_learn,
Welcome to the eazyBI community!
When you import data from CSV files, the measures are only mapped to the dimensions in the dataset where they arrive.
The measure “Row count” is mapped against the “Scale” dimension.
The measure “Planned” is mapped against the “Resourceid” dimension.
However, these measures are mapped against the “Project” dimension.
Therefore, you might use the Project dimension to filter by Scale and retrieve the relevant values for Planned.
Please read more about creating a calculated measure here - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members.
The expression for the calculated measure might be as follows.
Sum(
Filter(
--retrieve the set of relevant projects
DescendantsSet(
[Project].CurrentHierarchyMember,
[Project].[Project]),
--filter by measure relevant to one dataset
--defaultcontext resets dimensions not specified
DefaultContext(
([Measures].[Row count],
[Project].CurrentHierarchyMember,
[Scale].CurrentHierarchyMember)
)>0
),
--actual measure to sum for the filtered project
--defaultcontext resets dimensions of the other dataset
DefaultContext(
([Measures].[Planned],
[Project].CurrentHierarchyMember,
[Resourceid].CurrentHierarchyMember)
)
)
The result might look like this.
You can read more about the used functions DescendantsSet() and DefaultContext() in documentation here - MDX function reference.
Regards,
Oskars / support@eazyBI.com
