Hi
We are doing some PoC with PowerBI to report out of essbase cube.
With the advanced Editor and some custom code, I could connect to Essbase and apply some filter criteria.
But as Essbase allows ragged hierarchy and We have lots of requirements to show all the bottom level members below a specific parent. I am struggling to have that working in PowerBI.
As PowerBI works based on Levels, I am struggle to create a filter that would allow me to use LEAVES(<Member>).
From Essbase MDX trace, I can see that the MDX has been run successfully.
But PowerBI reports the error
Feedback Type:
Frown (Error)
Error Message:
The ReadElementContentAsString method is not supported on node type EndElement. Line 145, position 3.
Below is a sample which is not working at the moment.
#"Added Items" = Cube.Transform(CostApp.CostDB,
{
{Cube.AddAndExpandDimensionColumn, "[Years]", {"[Years].Levels(1)"}, {"Years"}},
{Cube.AddAndExpandDimensionColumn, "[Currency]", {"[Currency].Levels(1)"}, {"Currency"}},
{Cube.AddAndExpandDimensionColumn, "[Period]", {"[Period].Levels(1)"}, {"Period"}},
{Cube.AddAndExpandDimensionColumn, "[Scenario]", {"[Scenario].Levels(1)"}, {"Scenario"}},
{Cube.AddAndExpandDimensionColumn, "[Version]", {"[Version].Levels(1)"}, {"Version"}},
{Cube.AddAndExpandDimensionColumn, "[CC_Department]", {"[CC_Department].Levels(5)"}, {"Department"}},
{Cube.AddAndExpandDimensionColumn, "[CC_DataSource]", {"[CC_DataSource].Levels(1)"}, {"DataSource"}},
{Cube.AddMeasureColumn, "Total Costs", "[Total Costs]"}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([#"Years"]) = "{[FY19]}"
and Cube.AttributeMemberId([#"Currency"]) = "{[GBP]}"
and Cube.AttributeMemberId([#"Period"]) = "{[YearTotal]}"
and Cube.AttributeMemberId([#"Scenario"]) = "{[Actual]}"
and Cube.AttributeMemberId([#"Version"]) = "{[Final]}"
and Cube.AttributeMemberId([#"Department"]) = "{DESCENDANTS([Total Support Services],1)}"
and Cube.AttributeMemberId([#"DataSource"]) = "Leaves([TotalDS])"
)
)
Will be greatly appreciated, if you have any Custom Functions/workaround for similar queries.
