Filter propagation without bi-directional relationships

[Rakel]

Hello,

I suppose this is a dummy question (or at least very simple), but honestly I need some support to sort it out as I am a beginner on Power BI. 

I have a star schema in my data set with a fact table (sales) and two dimensions (product and cities). The relationship between fact table and dimensions are 1 to many. 

There is a slice for each dimension and a table for the fact table.  If I filter on city or in product, sales table is properly shown. But, now I want that when I filter in one of the dimension tables (products or cities) the options available in the other filter (cities or products) will be also filtered.

For instance, if I select a city, then if I go to product slice, I want to see only the products available on that city and in the other way, if I filter a product and then I go to city filter, only cities having that product have to be shown.

The only way I am getting the desired data is changing the directions to bi-directional but I've been reading in previous questions on this forum that this has to be avoided because of performance issues.

The solution seems to be use the CROSSFILTER() function but I don't have an idea on how to use it. Do I have to use on both dimensions? 

Is there any article /example I can read on this topic? 

Thanks in advance,

      Raquel 

[rargyle]

I acheive this by having a measure in the fact table, calculating number of products. Then add that measure to each visual slicer, and set it to only show where number > 0.

[Rakel]

any help on this topic?

[Greg_Deckler]

@Rakel You use CROSSFILTER by specifying the names of the columns that form the relationship and use "Both". There are other ways, you can use DISTINCT along with the IN operator and potentially even TREATAS. Sample data would assist tremendously along with expected output.

CROSSFILTER function - DAX | Microsoft Learn

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Rakel]

@Greg_Deckler Thanks for your reply.

Let's see if I am able to explain it better...

This is my model 

And these are my visualizations:

As you can see, there are two slicers (one for product and another one for region) and a table showing product description , unit price and total product cost (those fields are not relevant).

What I want to get is that when I filter in one region (France, for instance) in the Product slicer only appear the list of products for France. Currently, the full list is available.

Currently, this is what I get:

The only way I get the goal is changing the relationship direction to both. What I get then is Product slicer filtered showing only the list of products for France:

So, basically, my question is how to "make" a relationship between two dimension tables without bi-directional relationship. 

Hopefully I am clearer now.

Thanks,

   Raquel