Forum Discussion
Using Filter Plane filtering with Hierarchies
I'm running into this issue with using the filter plane with a hierarchy.
For example, I have the hierarchy as follows:
Product Category
Product Segment
Product
When I use a the filter plane to search for greater than 10k and lower than 50k, it filters the data using the lowest level of the hierarchy. Is there a way to have the Filter plane filter to the current visible Hierarchy? I'm using the Drill down functionality. If there is another I can do this that be great too.
3 Replies
Hi,
According to your description, I create a table to test.
Here is my test table and its matrix visual:
Please take following steps:
1)Create a measure to replace the [Sales] column:
Measure =
IF (
ISINSCOPE ( Table2[Product] ),
IF ( SUM ( Table2[Sales] ) > 100, SUM ( Table2[Sales] ), BLANK () ),
IF (
ISINSCOPE ( Table2[Product Segment] ),
IF ( SUM ( Table2[Sales] ) > 100, SUM ( Table2[Sales] ), BLANK () ),
IF (
ISINSCOPE ( Table2[Product Category] ) ,
IF ( SUM ( Table2[Sales] ) > 300, SUM ( Table2[Sales] ), BLANK () )
)
)
)
You can change the number in this measure or delete the ‘IF’ to calculate the level you want and the outer ‘IF’ points to the lowest level.
2)For this conditional filter, the result shows:
Here is my test pbix file:
Best Regards,
Giotto Zhi
That is useful, but I was referring the the filter pane on the right side.
I want to use this drill downs conjunction with the filter above
What happening is that regardless of the Level im at it will always filter the data using the lowest level in the hierarchy.
Best Regards,
Tuan
Hi,
For your requirement, I filter [Product Category] as an example.
Please take following steps:
1)Create a parameter:
2)Then try this measure:
Filterby Category =
VAR result =
CALCULATE (
SUM ( 'Test'[Sales] ),
FILTER (
ALLSELECTED ( 'Test' ),
'Test'[Product Category] = SELECTEDVALUE ( Test[Product Category] )
)
)
RETURN
IF (
result >= MIN ( 'CategoryFilterTable'[CategoryFilterTable] )
&& result <= MAX ( 'CategoryFilterTable'[CategoryFilterTable] ),
1,
0
)
Then set filters:
3)Set category filter:
And it shows:
The other two level’s setting is similar to above.
Here is my test pbix file.
Here is my first reply improved pbix test file.
Note that both of these two ways cannot completely reach your requirement.
Best Regards,
Giotto Zhi
