cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KevinGesquiere Frequent Visitor
Frequent Visitor

Related parent of selected value in slicer

I have a slicer on page 1 which usage is a report filter and the items showing here are the parents. On page 2 I also have a slicer with the children items of the selected parent on page 1. I want to create a measure which is showing me the parent(s) of the selected child(ren) together with the selected items of slicer 2. 

 

If the user first filters the category on page 1, then goes to page 2 and filters the desired products I can create a measure in the form of "Filtered on Cat1 - Products Prod1, Prod2, Prod3"

 

But if the user first goes to the page 2 and filters some products, I can't figure out how to get the parent(s). There is a relationship between those two. The measure should then be: "Filter on Cat1, Cat3 - Products Prod1, Prod5". Is this possible?

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Related parent of selected value in slicer

you need to change the cross filter direction to 'Both' from 'Single'
right now Category propagates filter to SubCategory, but not the other way round

3 REPLIES 3
Super User
Super User

Re: Related parent of selected value in slicer

this should work:

FiltersApplied=
"Filter on " & CONCATENATEX ( VALUES ( Table1[Cat1] ), [Cat1], ", " )
    & " Products "
    & CONCATENATEX ( VALUES ( Table1[Cat2] ), [Cat2], ", " )
KevinGesquiere Frequent Visitor
Frequent Visitor

Re: Related parent of selected value in slicer

 @Stachu thanks for your post.

 

The problem here is all the values from the parent are shown in the measure, which is not want I wanted.

Let me explain a bit more with an example on the AdventureWorks db. 

- Page 1: slicer with the ProductCategory names

- Page 2: slicer with the ProductSubCategory names

- Slicer on page 1 will sync with with the other pages. 

 

FiltersApplied = 
VAR ISCATEGORYFILTERED = (ISFILTERED('Production ProductCategory'[Name])) 
VAR ISSUBCATEGORYFILTERED = (ISFILTERED('Production ProductSubcategory'[Name]))
RETURN
IF (ISCATEGORYFILTERED = FALSE() && ISSUBCATEGORYFILTERED = FALSE(); "No filters applied";
    IF(ISCATEGORYFILTERED = TRUE() && ISSUBCATEGORYFILTERED = FALSE(); "Filter on: " & CONCATENATEX( VALUES( 'Production ProductCategory'[Name]); 'Production ProductCategory'[Name]; ",");
        IF(ISSUBCATEGORYFILTERED = TRUE();"Filter on: " & CONCATENATEX(VALUES('Production ProductCategory'[Name]);'Production ProductCategory'[Name];",") & " Products " & CONCATENATEX(VALUES('Production ProductSubcategory'[Name]); 'Production ProductSubcategory'[Name];","))))

In case no filters are applied; I get the text: "No filters applied". -> OK

In case filter is applied only on page 1; I get : "Filter on Bikes, Accessories" -> OK

In case filter is only applied on page 2; I get : "Filter on Bikes, Components, Clothing, Accessories, Products: Brakes". -> NOK.

In the last case I should "Filter on Components - Products: Brakes" but not all the available categories. 

 

I don't find a solution how to get the parents name of a selected child... 

 

 

 

Super User
Super User

Re: Related parent of selected value in slicer

you need to change the cross filter direction to 'Both' from 'Single'
right now Category propagates filter to SubCategory, but not the other way round