Filteringh to reacts to another filtering

[Chrisjr]

Hello All, 

I have the following model:

You will notice there is a bidirectional relationship between the Promo type table and the fact table as well as between the competitors lookup table and the fact table. 

The reason for these 2 bidireactinal relationships is that on the front-end, when I apply a filter from the Promo Type table, I want the other filter from the competitor table to show me only the possible competitors I can filter based on my first filtering from the Promo Type table. 

Is it a good method to achieve it or is there another way to do so whithout having to use a bidirectional relationship? 

Thanks, 

Chris

[Chrisjr]

Thanks a lot @v-xiaosun-msft 

[v-xiaosun-msft · Accepted solution]

Hi @Chrisjr ,

According to your description, I made a sample and here is my solution.

Sample data:

Realtionships:

Create a measure.

Measure =
VAR _typeid =
    SELECTEDVALUE ( LOOKUP[promo type ID] )
RETURN
    IF ( MAX ( 'FACT'[promo type ID] ) = _typeid, MAX ( 'FACT'[Competitor ID] ) )

Put "promo type ID" from "promo type_LOOKUP" TABLE and "Competitor ID" from "Compititors_lookup" Table into slicers.

Put "Measure" into the filters from second slicer and selecte "Not blank", you will get the expected output.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.