Power BI Desktiop: Problem with more than one bidrectional cross filter relationship!

[Anonymous]

Hello!

I basically need to cross filter a table in both directions for multiple relationships and Power BI will not let me do this. This is the example report to replicate my problem:

For example, I need to filter from Data[Date] to Thresholds[Type] and the other way round. The tables Data and Thresholds are connected trough Project. So in order to do this, I would need to set the cross filter direction between Data and Project to both (as shown in the Screenshot). I also need to set the cross filter direction between Project and Threshold to both. However, Power BI tells me that this is not possible, because there is already an existing bidirectional relationship for this table.

One solution someone told me, was to merge the Project table with the Treshold table, but the Project table contains only one row per project whereas the table Tresholds has multiple entries per project. I do not think merging them is a good idea in this case (See the tables below:)

Project table:

Project	Start date	Venue
A	01.01.2021	London
B	01.02.2021	Madrid
C	01.03.2021	Paris

Threshold table:

Project	Type	Warning	Error
A	Value 1	7	14
A	Value 2	3	7
B	Value 1	14	21
B	Value 2	5	10
C	Value 1	7	14
C	Value 2	3	5

Data table

Project	Object	Date	Value 1	Value 2
A	A1	01.01.2021	10	0
A	A2	01.01.2021	9	2
B	B1	01.01.2021	15	4
C	C1	01.01.2021	6	6
A	A1	08.01.2021	16	1
A	A2	08.01.2021	8	4
B	B1	08.01.2021	22	6
C	C1	08.01.2021	8	4
A	A1	15.01.2021	21	0
A	A2	15.01.2021	6	3
B	B1	15.01.2021	30	12
C	C1	15.01.2021	6	2

Project Info table

Project	Object	Project leader
A	A1	Tom
A	A2	Anna
B	B1	Peter
C	C1	John

I have encountered this problem a couple times already and sometimes I could find a way around this problem but this time I am stuck. The problem is, that in the real report I have so many different tables and I always need to filter in both directions. Does anybody have an idea, how to work around that? Or maybe how to change the dataset?

Thank you for your help and kind regards!

[v-cazheng-msft]

Hi @Anonymous 

Not sure about the tables in your model. If there are only these four tables in your model, you can successfully create relationships for these tables. For more details, you can refer the attached pbix file.

But you can indeed consider merge Project table and Threshold and it will simplify the relationships in your model which is beneficial to report load and dataset refresh. In addition, such operations will not have a huge impact on your data structure.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-cazheng-msft 

Thank you for your answer. I checked your pbix file but unfortunetely it does not solve my problem.

For example, if I create I table in your file with the column 'Date' from your 'Data table' and 'Project' from your 'Threshold table' it does not work. The visual cannot be displayed and Power BI tells me that it is because of a missing relationship. 

I also found this entry Get "Missing Relationship" and "Can't Display the ... - Microsoft Power BI Community that is similar to my own problem because it has even more tables but unfortunetely it does not have a solution.

The problem with merging those tables is that - unlike in this example report - I have even more tables with that 'missing relationship' problem and merging all of them into the projects table would make it to big. The reason is, that I need the bidrectional relationships for a variety of different slicers that need to be synched with each other.

[HarishKM]

@Anonymous  Hey 

1) You can use merge query so you get your desired column based on your requirement .

2) You can create a key column based on project . you can use conditional column like if project project A then output will be 1 like that . you need create it for both table . then define your table relationship.

Try that and let me know .

[Anonymous · Accepted solution]

Hi @HarishKM 

thank you for your reply!

I don't understand exactly what you mean by your suggested solutions. Could you explain it in more detail?

I found, however, this idea: https://www.sqlbi.com/articles/syncing-slicers-in-power-bi/
This still would not help with trying to combine the values 'Date' from the 'Data' and 'Project' from 'Threshold' into a single table visual but it does help with trying to sync slicers (which I was also looking for). The idea is to use measures for the slicers instead of using bidirectional relationships in the data model.