Filters don't work on an OLAP SSAS Parent dimension

[DeepButi]

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]

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]

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 🙂  ). 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 😞

[DeepButi]

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.

All data okOne level1 value selectedLook at all filters

[DeepButi]

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)

[v-huizhn-msft]

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]

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.

Excel

[DeepButi]

No one working with parent-child dimensions?

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

[BetterCallFrank]

we stumpled upon the same thing.

interestingly in excel pivot tables, not in power bi.

the basic filter does not search on intermediate levels, the advanced filter does - this is very confusing for users... I'd also like to know how we can bring this to the dev-teams attention.

[Anonymous]

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

[DeepButi]

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?