cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JMPowerBI
Helper I
Helper I

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

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:

JMPowerBI_0-1619014692556.png

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:

ProjectStart dateVenue
A01.01.2021London
B01.02.2021Madrid
C01.03.2021Paris

 

Threshold table:

ProjectTypeWarningError
AValue 1714
AValue 237
BValue 11421
BValue 2510
CValue 1714
CValue 235

 

Data table

ProjectObjectDateValue 1Value 2
AA101.01.2021100
AA201.01.202192
BB101.01.2021154
CC101.01.202166
AA108.01.2021161
AA208.01.202184
BB108.01.2021226
CC108.01.202184
AA115.01.2021210
AA215.01.202163
BB115.01.20213012
CC115.01.202162

 

Project Info table

ProjectObjectProject leader
AA1Tom
AA2Anna
BB1Peter
CC1John

 

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!

1 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.

View solution in original post

4 REPLIES 4
v-cazheng-msft
Community Support
Community Support

Hi @JMPowerBI 

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.

 

v-cazheng-msft_0-1619158926437.png

 

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.

v-cazheng-msft_1-1619158926440.png

 

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.

 

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
Responsive Resident
Responsive Resident

@JMPowerBI  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 .

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.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors