How to filter a table column with the selection on Other Table Column

[somnath6309]

Hi,

In a tutorial we have seen the following report :

In the report if Customer Name e.g. Adams Blake is selected the matrix shows the Brands which were purchased by customer along with sales amount. However, in the second slicer we could not see the product names which were purchased by Adams. As we know that a filter from Customer easily reaches to Sales ( for one to many relationship) but from Sales the same is not propagated to Product because of Many to One relationship and following is the screen shot of data model:

In the tutorial we have seen that the relationship between Sales and Product was made Bidirectional and after that we achieved the desired report i.e. we can see the product names along with Brands when a specific customer is selected and following is the screen shot of the same:

However it is advised that the bidirectional relationship should not be activated for the entire data model as the same can create many complications and unexpected results.

Now the question is what will be the formula based solution to achieve the aforesaid situation where we can see Product names along with brands when a specific customer is selected. The same has not been told in tutorial but in practical scenario we can face such setuation and this will be very helpful for us if we get a code based solution of the same.

Regards, Somnath

[Anonymous]

Hi @somnath6309 ,
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[mark_endicott]

@somnath6309 - Add your Sales measure to the slicer on the filter pane in the "Filters on this visual" part, then change it to show items when the value "is not blank", see screenshot below:

This passes any filter placed from any dimension table onto a fact table back to any other dimension. Because if Adams Blake has not bought a product, then the Sales measure will be blank for that product. This way you also do not need to create additional measures for each dimension. 

If this works for you, please accept as the solution as it helps users with the same challenge. 

[johnt75]

Create a measure

Product is visible =
IF (
    SELECTEDVALUE ( 'Product'[Product Name] )
        IN SUMMARIZE ( Sales, 'Product'[Product Name] ),
    1
)

and add that as a filter to the slicer, to show only when the value is 1.