cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vrocca
Advocate IV
Advocate IV

Bi-directional filter on conformed dimension

Hi all,

 

I have a Tabular model in which a dimension has a relationship to 2 fact tables. I would like to use this dimension as a slicer in my Power BI report, and only show values for which data exists in either/both of these fact tables, as well as to have the data in that dimension filtered when the fact table is sliced by another dimension.

 

Before bringing in the second fact table, I had a bi-directional filter between the dimension and the fact, and the data filtered just fine in the slicer. However, when I tried creating the relationship between the same dimension and the second fact table, it wouldn't let me apply the bi-directional filter due to ambiguity.

 

So now I have this one-way filter direction between dim-Fact1 and dim-Fact2, and the issue is that all the values from the dimension appear in the slicer. Is there a way to make this work? In the Multidimensional world this wasn't an issue.

 

Thanks in advance!

Victor

Thanks,
Victor Rocca | www.victorrocca.com
6 REPLIES 6
v-qiuyu-msft
Community Support
Community Support

Hi @vrocca,

 

How did you connect to tabular model, Import mode or Live connection? Where did you create the bi-directional relationship and error threw out? On SSAS side or Desktop?

 

If it's in SSAS project, please take a look at ferzfeld's suggestion in this thread: Error message: Excel allows only one filtering path between tables in a data model.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-qiuyu-msft,

 

I connect to the Tabular model via Live connection. I'm trying to create the bi-directional relationship in SSAS, and that's where I'm getting the error. I tried adding the relationship by using the method suggested in the post you mentioned, but when I try to deploy or open the model I get a similar error about amiguity so I'm unable to add it. 

Thanks,
Victor Rocca | www.victorrocca.com
Anonymous
Not applicable

Hey @vrocca, usually the ambiguity error is due to multiple possible filter paths. Is there anything else connected to your dimension? Could you post a screenshot of the data model relationships?

Hi @Anonymous, yes, my dimension is a conformed dimension that is linked to another fact table.

 

Below is a basic sample of the model I'm working with. I have 2 dimensions (say Date and Product) and 2 fact tables (say Sales and Returns).

 

In the first screenshot you can see that both dimensions have relationships to both fact tables in a 1-many format. If I try to make the filter in any of those relationships to be bi-directional, I get an error. The only way I can add it is if I delete or make inactive one of the relationships (screenshot 2).

 

Ideally I would like to have these dimensions be filtered by what's in the fact tables, so that when a user views the report and wants to see Sales/Returns for a Product, they only see a list of Products (in a report Slicer) for which there is data (in either of the fact tables)

 

Screenshot 1Screenshot 1

 

 

Screenshot 2Screenshot 2

Thanks,
Victor Rocca | www.victorrocca.com

Similar model in Power BI Desktop with a data import. I'm able to create 1 bi-directional filter, but unable to add it to any of the other relationships.

Power BI - Only 1 Bidirectional Filter.png

 

Thanks,
Victor Rocca | www.victorrocca.com
Anonymous
Not applicable

Hey @vrocca that helps, thanks! It looks like adding bi-directional filtering would create circular filtering paths, which is not allowed. If you changed the yellow highlighted relationship to bi-directional, then when you filter the Dim on the top left, it would filter the Fact on the bottom right, which would filter the Dim on the top right, which would filter the Fact on the bottom left, which would filter the Dim on the left - and you end up in a circle.

 

I believe you should be able to add bi-directional filtering between the top left Dim and the bottom right Fact. Does it allow you to do that?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.