Bi-directional filter on conformed dimension

[vrocca]

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

[v-qiuyu-msft]

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

[vrocca]

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. 

[Anonymous]

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?

[vrocca]

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 1

Screenshot 2

[vrocca]

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.

[Anonymous]

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?