cancel
Showing results for 
Search instead for 
Did you mean: 

PBI RS Jan 2021: Microsoft Native Hierarchy Slicer with Search Option Issue

Hi There,

Currently we are using the January 2021 PBI RS native Microsoft slicer using the hierarchy functionality (adding multiple columns as layers). In the process have also turned on the "Search" capability. When using the search option, it narrows down the selection in any level of the hierarchy. Example: Searching for the word "Ford" would look at both the Vehicle and Brand column as pictured below. It then narrows down the selection to only showing items with "Ford".

SarQL_0-1628096209798.png

Now, if there are many different versions of the word Ford the list starts to become large. You then think that when selecting the level above it > Cars, as a fast route instead of selecting each individual version of Ford, you would only get a count with what you searched for originally.

 

That is not the case. You are actually getting that level that you clicked on, count of Cars regardless of the brand (in this example 6 instead of desired 3). Ideally if you are using a search option and only seeing results for that lowest level, you would want a way to quickly select those options without having to individually click on them.
Actual Behavior:

SarQL_3-1628096740815.png

 

SarQL_1-1628096583706.png

Expected Behavior: (notice I had to select each individual lowest level option to get the count)

SarQL_2-1628096667477.png

 

 

Status: New
Comments
v-yingjl
Community Support

Hi @SarQL ,

The 'Search' feature in the slicer is just to make the slicer look more clear and convenient to find the corresponding fields in it, it would not affect any other visuals in Power BI Desktop.

 

If you want to calculate the count as your mentioned, you can try to create a measure like this:

Count = 
CALCULATE(
    DISTINCTCOUNT('Table'[Brand]),
    FILTER(
        ALLSELECTED('Table'),
        CONTAINSSTRING('Table'[Brand],"Ford")
    )
)

 

Best Regards,
Community Support Team _ Yingjie Li

justaslam
Regular Visitor

Power BI refresh error “could not load file or assembly…provided impersonation level is invalid”

SarQL
New Member

Hi @v-yingjl ,

Unfortunately the DAX measure will not work for the use case. We would not like to hard code a value that will be searched, our users will want to search things dynamically so the ContainsString can't be used. Now, if there is a DAX measure that would only consider doing a distinct count at the lowest level of a hierarchy selected, that could possibly work - I'm not sure what this would look like or if it exists. Thoughts?

Thanks for your help.