cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DeepButi Regular Visitor
Regular Visitor

Filters don't work on an OLAP SSAS Parent dimension

We have an OLAP SSAS cube with an Usage->Parent dimension.

 

Dimension data includes

- Key

- Parent

- Name

 

Data is structured at three levels where Parent points to the Key of its parent.

Key is defined with NameColumn->Name

 

So the users see

 

GrandFatherName ..... FatherName ..... SonName ....................... SonData

 

But we cannot make any filter to work on PowerBi.

When a GrandFatherName is selected, no data is displayed

When FatherName is selected, no data is displayed

When SonName is selected .... well, this is still more unusual, there is no SonName list, we get a list of <blank> values! so we don't know which Son are we selecting, but nevertheless, again no data is displayed.

 

Accessing the cube using Excel and a pivot table works as expected, any level of data can be explored and selected.

 

Any idea what can be wrong?

9 REPLIES 9
DeepButi Regular Visitor
Regular Visitor

Filters don't work on a Parent-Son dimension

We have an OLAP SSAS cube with an Usage->Parent dimension.

 

Dimension data includes

- Key

- Parent

- Name

 

Data is structured at three levels where Parent points to the Key of its parent.

Key is defined with NameColumn->Name

 

So the users see

 

GrandFatherName ..... FatherName ..... SonName ....................... SonData

 

But we cannot make any filter to work on PowerBi.

When a GrandFatherName is selected, no data is displayed

When FatherName is selected, no data is displayed

When SonName is selected .... well, this is still more unusual, there is no SonName list, we get a list of <blank> values! so we don't know which Son are we selecting, but nevertheless, again no data is displayed.

 

Accessing the cube using Excel and a pivot table works as expected, any level of data can be explored and selected.

 

Any idea what can be wrong?

v-huizhn-msft Super Contributor
Super Contributor

Re: Filters don't work on an OLAP SSAS Parent dimension

Hi @DeepButi,

What model do you use, SSAS Multidimensional Models or Tabular Model? 

Based on what I know, In excel, it worked correctly because you connect SSAS live. In Power BI desktop, I guess you use import mode, right? Becasue, if you get data from SSAS using live connection, the levels between parants and sons will be uploaded, and you can use them. While if you get data using import mode, you just get resource table without relationship between. You need to create relationship between tables if you want to use the three levels.

Please review the following articles.

Connect to SSAS Multidimensional Models in Power BI Desktop
Using Analysis Services Tabular data in Power BI Desktop

Best Regards,
Angelia

DeepButi Regular Visitor
Regular Visitor

Re: Filters don't work on an OLAP SSAS Parent dimension

We use a Multidimensional model. We get data in live mode, all relations and formulas are imported, no need to define anything (in fact it would be a huge task as there are more than one hundred calculated members Smiley Happy  ). We have dozens of PowerBi charts using all kind of relations, filters, whatever ... (I mean, we are not newbees, we have been using SSAS for years and have a lot of experience with PowerBi).

 

The parent-son relationship works perfectly well on all charts ... but we cannot use it in filters. As soon as we select a value on the filter, the chart doesn't show any values. And as pointed out, the last level of the filter shows <blank> for all rows as values instead of the true values.

 

Probably we have something wrong somewhere but we cannot figure it out Smiley Sad

DeepButi Regular Visitor
Regular Visitor

Re: Filters don't work on an OLAP SSAS Parent dimension

Image1: data displayed correctly with parent-son relationship deployed at its maximum of three levels.

Image2: one value of the first level selected, no data

Image3: look at the three filter fields, level1 and level2 (third one alphabetically) have valid values, level3 show <blank> for all rows.

 

Image1.jpgAll data okImage2.jpgOne level1 value selectedImage3.jpgLook at all filters

DeepButi Regular Visitor
Regular Visitor

Re: Filters don't work on an OLAP SSAS Parent dimension

Wow! Just playing a little bit and I discovered advanced filters work as expected at all three levels! A "Containg xxx" filter shows the expected values. Basic filter doesn't.

 

With an advanced filter set at level1, level3 basic filter shows all correct values (but again, clicking one of them shows no data)

 

Image4.jpg

v-huizhn-msft Super Contributor
Super Contributor

Re: Filters don't work on an OLAP SSAS Parent dimension

Hi @DeepButi,

From your screenshot, your hierarchy is a parant-child hierarchy. Because there is only one member in the first level of parant-child hierarchy. In the screenshot "one level1 value is selected", there is no data whatever value you selected? And could you please share a screenshot working in excel but not in Power BI?

Best Regards,
Angelia

DeepButi Regular Visitor
Regular Visitor

Re: Filters don't work on an OLAP SSAS Parent dimension

Hi @v-huizhn-msft

 

From your screenshot, your hierarchy is a parant-child hierarchy.

Yes, this is stated clearly on my first post. 

 

Because there is only one member in the first level of parant-child hierarchy.

Incorrect. There are some thousand members at the first level (with parent=null)

 

In the screenshot "one level1 value is selected", there is no data whatever value you selected?

Correct. No matter what value is selected, nothing is shown on the grid.

Look at the fourth image on my third post ... advanced filter with "contains" work as expected. But we need basic filter to work as it is used as a Drillthrough filter and there is no way to use advanced filters on Drillthorugh.

 

And could you please share a screenshot working in excel but not in Power BI?

Yes, sure, annexed. The filtering popup menu doesn't show up on the PrintScreen, but it works as expected.

 

Image5.jpgExcel

 

DeepButi Regular Visitor
Regular Visitor

Re: Filters don't work on an OLAP SSAS Parent dimension

No one working with parent-child dimensions?

 

Should we consider this a bug? Any way to send it to PowerBi development team?

apfenech Frequent Visitor
Frequent Visitor

Re: Filters don't work on an OLAP SSAS Parent dimension

Did you ever find a solution for this?  I'm having the exact same issue and can't find anything helpful...