cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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?

10 REPLIES 10
Highlighted
Helper I
Helper I

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?

Highlighted
Microsoft
Microsoft

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

Highlighted

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 😞

Highlighted

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 okAll data okOne level1 value selectedOne level1 value selectedLook at all filtersLook at all filters

Highlighted

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

Highlighted

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

Highlighted

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.

 

ExcelExcel

 

Highlighted

No one working with parent-child dimensions?

 

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

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors