ISFILTERED function always returns the lowest hierarchy level

[jstimac]

ISFILTERED function always returns the lowest hierarchy level and not the hierarchy level being actively filtered. I noticed this change in last few days, before it was returning only the hierarchy level actively filtered. Any chance the functionality will return to previous state so one can make dynamic titles based on current selections?

[liprec]

The query the HierarchySlicer is creating indeed a selection of the lowest level. This change was needed to make sure that the slicer still works in the future and also support bigger selections, but causing the ISFILTERED functionality been broken. I will look into this for a future release, but I cannot promise anything.

[olesojg]

Making sure I understand this. Prior to this change, the Dax created would be on the active filter selection in the tree (highest level selected). Now, it's going to use the lowest levels to create the Dax filter. I have seen our performance drop the past couple days when selecting an item; taking a long time to return. If you could provide a brief statement on the logic of the control, it would be much appreciated.

[liprec]

One thing: the HierarchySlicer or any other visual is not generating DAX, but all visual interactions is done by schematic queries (SQ). And Power BI is translating those SQs to DAX. But the big difference between the old version and the current version is that the current version is creating a SQ using the IN statement with all the leaves. And the old version created a (huge) SQ with a lot of AND and OR statements, but with a twist: if all leaves of a parent were selected, only the parent was used in the SQ. But now it looks like the rather effective IN clause is hitting a performance impact when translated to DAX.

EDIT (July 10th): I did some more research and with Power BI itself and AAS/SSAS2017 the IN statement of the semantic query will be translated to a DAX query with the new IN operator (https://docs.microsoft.com/en-us/sql/analysis-services/what-s-new-in-sql-server-analysis-services-2017?view=sql-server-2017#dax-enhancements). Looks like other combination will fall back to the AND/OR filter combination which will cause performance issues and the best solution is to upgrade to a newer version.

[olesojg]

So, do you control what values are passed to the SQs? Meaning, if I have a Hierarchy Slicer control 4 levels deep, and I select the top most value, I would only want that value in my dax statement. Say we have Account, Division, Region, Store and I select a specific account, I would expect the Dax to end up as "Account=XXX". With the new way, it's doing an IN statement with every leaf value in that Dax statement. Do you control this or the api you passing the SQ too? We are using Azure Analysis Services btw, so we would have the latest version, which is using the IN statement.

[liprec]

I do control the SQ that is constructed and at this moment it is always an IN statement based on the lowest level with all the children of the selected members. So in the case of 'Account=XXX' the filter is now an IN statement with all the stores that are part of XXX. In general there shouldn't be any performance issues, but I can imagine that there is some impact when there are a lot of children. I will look for a next version if there could be some improvements as indeed, when XXX is selected it should be faster to only select that account and not all children.

[danjenglish]

Just a heads up, we had to get an older version of the hierarchy slicer from Microsoft to use with our reports (I work with olesojg) and we needed the older functionality of just passing the parent hierarchy instead of also passing all of the children. The performance of the new slicer just brought some of our reports to a halt and it was not usable for large hierarchies. Just wanted to make you aware of that. Thanks.

[liprec]

With version 1.3 the ISFILTERED() functionality is added.

[danjenglish]

Hi Jan, does this release include the resolution of this issue as well then or do we need to make a separate issue just for this: I will look for a next version if there could be some improvements as indeed, when XXX is selected it should be faster to only select that account and not all children.

[liprec]

Yes, if you select only one level, the filter is only that level and selected member: so selecting 2018 at the year level will result in a filter Year=2018 Only if multiple selections are made, the filter is 'falling back' to selecting all children.

[danjenglish]

Ok, good to know. So if someone selects 2017 and 2018 then it would select all of the children and not simply Year IN (2017,2018)

[liprec]

Not yet, but that will be included in a future version. The current version was a quick win.

[danjenglish]

Just wanted to check in and see if there has been any development in this particular area to resolve passing the children members when multiple parent selections are made. Thanks!

[liprec]

There has been work in progress lately. The upcoming version is way better in detecting the 'highest' possible filter selection. So in the case of a (multi)selection of multiple years, it will return a filter with only the selected 'Years'. And if one month is deselected, the filter will be based on all selected 'Months'.

Only problem for now: I do not have an ETA for the new version.

[denglishbi]

This has not been released yet, correct? I did a quick test with 1.3.2 and it appears when I select to parent levels it goes to lowest level on the filter for both selected items. We would like to use a newer version than the one we are using that doesn't have this performance issue so that we can try and get our selection issues resolved in the hierarchy slicer. Thanks.

[liprec]

V1.3.2 is intermediate release fixing only a style issues caused by the Power BI service itself and does indeed not include any new functionality. The v2.x version will include the improved filter selection mechanism.

[antonyraj04]

If I select the parent (Highest ) level , it selected lowest level on the filter . Also IsFilter functions returns both selected items. It should return only selected level. Currently I am using V1.3.2 version. Shall I know , how I can resolved this issue ? Please advice me.

[liprec]

@antonyraj04, please wait for the new v2 version which will be released in a couple, of weeks. This version will provide the correct ISFILTERED() values.