Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Error while adding filter criteria to data source from Essbase adapter

Hi

We are exploring the feasibility of using powerBI for all the reports from the Hyperion Essbase.

 

We noticed that the latest PowerBI release had fixed some of the issues with Essbase adapter. I am now able to connect to Essbase using Import Data or Direct query. Both works and I am able to see data refreshed from the essbase cube. But as soon as I try to add filter criteria it breaks down. 

 

Because of the size of the cube, we like to add filter criteria to limit the dataset refreshed from Essbase.

 

Below is extracted from "Advanced Editor" generated by power BI when I try to add Filter to the datasource

 

#"Added Items" = Cube.Transform(Cost.Cost,
{

{Cube.AddAndExpandDimensionColumn, "[Years]", {"[Years].Levels(1)"}, {"Years.[Years].Level 1"}},
{Cube.AddAndExpandDimensionColumn, "[Currency]", {"[Currency].Levels(1)"}, {"Currency.[Currency].Level 1"}},
{Cube.AddAndExpandDimensionColumn, "[Period]", {"[Period].Levels(1)"}, {"Period.[Period].Level 1"}},
{Cube.AddAndExpandDimensionColumn, "[Scenario]", {"[Scenario].Levels(1)"}, {"Scenario.[Scenario].Level 1"}},
{Cube.AddAndExpandDimensionColumn, "[Version]", {"[Version].Levels(1)"}, {"Version.[Version].Level 1"}},
{Cube.AddMeasureColumn, "Total Costs", "[Total Costs]"}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([#"Years.[Years].Level 1"]) = "[FY19]" meta [DisplayName = "Y2019"]))
in
#"Filtered Rows"

 

Error Message:

Expression.Error: Essbase Error (1260052): Syntax error in input MDX query on line 9 at token ','

  • Anonymous's avatar
    Anonymous
    7 years ago

    Resolved it. Noticed that the filter values have to be enclosed in curly brackets 

     

    each (Cube.AttributeMemberId([#"Years"]) = "{[FY19]}")

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Resolved it. Noticed that the filter values have to be enclosed in curly brackets 

     

    each (Cube.AttributeMemberId([#"Years"]) = "{[FY19]}")