Showing results for 
Search instead for 
Did you mean: 
CPGRIER Frequent Visitor
Frequent Visitor

Essbase (Beta) Connector - Applying Filters?

Hi all,


Still trying out this connector, and having some trouble with it.  I realize it's still in Beta, but hoping someone can help me with using 'Cube' syntax...


I have figured out how to add items and see a dimension and a measure, but now I would like to apply a filter to one of the dimensions when I pull.  I have tried filtering after the pull and everything blanks out, so assuming I have to do this when I retrieve the data.  But I don't know the proper syntax to make this happen.  Below is what I have so far:


Source = Essbase.Cubes("http://xxxxxxxxxxxx/aps/XMLA"),
#"essbasecluster-2" = Source{[Name="essbasecluster-2"]}[Data],
AUTORPT = #"essbasecluster-2"{[Name="AUTORPT"]}[Data],
#"Added Items" = Cube.Transform(AUTORPT.AUTORPT,
{Cube.AddAndExpandDimensionColumn, "[AE]", {"[AE].Levels(2)"}, {"Accounting Entity"}},
{Cube.AddAndExpandDimensionColumn, "[YR]", {"[YR].Levels(1)"}, {"Year"}},
{Cube.AddMeasureColumn, "EBITDA", "[EBITDA]"}
#"Added Items"


Where can I place the filter in the above syntax?  I want to filter on '2018' data in the YR dimension.





Moderator v-yuezhe-msft

Re: Essbase (Beta) Connector - Applying Filters?


I don't have the Essbase data source. I make a test using SSAS cube, I can successfully filter the rows in Query Editor after loading all the data, and a Filter rows step will be automatically generated in Advanced Editor.


In your scenario, could you please post a screenshot about how you filter the Year column in Query Editor?


Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CPGRIER Frequent Visitor
Frequent Visitor

Re: Essbase (Beta) Connector - Applying Filters?

Thanks for your reply.  Unfortunately, I don't think a screen shot will help as everything is blank.


I believe this has to do with my essbase pull not having any parameters for the dimensions.  When I choose the dimensions and measure I don't know how, in the syntax, to apply parameters to the dimensions so that I see data.  For example, for the 'Department' dimension I want to only see 'Technology and Product'.  I need to 'populate' every dimension for this to return data, or that is my guess as to why this isn't working.  But would appreciate anyone who has this connector working to share how.  Again, I can connect and see the dimensions and measures, but the cube won't show me any data until I can somehow figure out how to apply parameters to the dimensions.  It's just like going into SmartView.  At first, nothing appears except the dimension names because the user hasn't updated them with the area he wants to see.


I have to admit, the Tableau connector is a lot more straight forward that this.  I realize this is a beta, but I hope the Jan 2019 update addresses this as many of us use Essbase.


Thanks everyone!!!

dpjan Frequent Visitor
Frequent Visitor

Re: Essbase (Beta) Connector - Applying Filters?

Agree with this.


Been trying to connect directly to Essbase but it keeps crashing because we can't select/input the parameters of dimensions to pull. Would love to find out how folks made this work with large datasets.



Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 115 members 1,191 guests
Please welcome our newest community members: