We’re looking to extract the COUNTRY value as a dimension from project country, to achieve so, in the source data we have created a custom field(country) to be used as a dimension.
However, we’re unable to link it to the category so when this dimension returns values i.e project count, it does so filtered by each country, using it as a category.
As shown in the screenshot, it appears to repeat.
With the following code, we have managed to extract the country as a dimension but still it isn’t doesn’t work as a category:
--Country
CASE
WHEN [Project.Category].[Category].CurrentHierarchyMember.Level.Name = 'Category'
THEN Left(
[Project.Category].[Category].CurrentHierarchyMember.Name,
InStr([Project.Category].[Category].CurrentHierarchyMember.Name,Chr(95))-1
)
WHEN [Project.Category].[Category].CurrentHierarchyMember.Level.Name = 'Project'
THEN Left(
[Project.Category].[Category].CurrentHierarchyMember.Parent.Name,
InStr([Project.Category].[Category].CurrentHierarchyMember.Parent.Name,Chr(95))-1
)
END
Any help is highly appreciated!
