Bi-Directional filtering

[dwhittaker1]

I have two tables, the first table is the QuoteLineItem table the second table is the QuoteHeader Table. The QuoteHeader Table is the one side of a 1 to many relationship with the QuoteLineItem table. The tables are related based on Quote id. I would like the ability to slice the QuoteHeader Table by Part Number, Customer number and description (these 3 are located on the QuoteLineItem table). I can do this if I set the cross-filter direction as both for the relationship between the two tables but I have read that I should not do this. Is it safe for me to set up this bidirectional relationship? Is there another approach I should take?

Quote Header Table

QuoteId	Quote	Account	Quote Site	Amount	CM%	Status
BAC	3468	TREVES	MONTERREY	$141,079	61.28	Approved

QuoteLineItem Table

QuoteId	Quote	Quote Line	cust Part #	Part #	Description
BAC	3468	A	2371	ABC	Retainer
BAC	3468	B	2371	ABD	Retainer
BAC	3468	C	2642	BAS	Retainer
BAC	3468	D	2711	SAD	Retainer
BAC	3468	E	2711	LWI	Retainer
BAC	3468	F	3012	CDC	Retainer
BAC	3468	G	3012	AJK	Retainer

[v-pnaroju-msft]

Hi dwhittaker1,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Hi dwhittaker1,

We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[johnt75]

There's no need to make the relationship bi-directional. You can create a measure like

Quote Header is Visible =
IF (
    SELECTEDVALUE ( 'QuoteHeader'[Quote ID] )
        IN VALUES ( 'QuoteLineItem'[Quote ID] ),
    1
)

and use that as a filter on the visual, set to show only when the value is 1.

[v-pnaroju-msft · Accepted solution]

Thankyou, @Irwan, for your response.

Hi dwhittaker1,

We thank you for your question in the Microsoft Fabric Community Forum.

As I understand, you are right to be careful about using bidirectional relationships. They can be useful but should be used carefully.

By default, Power BI relationships go from one side to many. So, filters from the QuoteLineItem table (many side) do not affect the QuoteHeader table unless you switch on bidirectional filtering.

For simple and medium sized models, it is okay to set the cross-filter direction to Both, especially if there are no circular relationships, the dataset is not very big, and you clearly understand how filters work. In these cases, using bidirectional filtering is an easy solution and works well.

If your model is large or complex, or if you want to avoid bidirectional filtering, you can use a disconnected dimension table and apply filters with TREATAS in a measure. But for your current case, this is not needed unless there is a performance problem.

Also, please check these links:
Model relationships in Power BI Desktop - Power BI | Microsoft Learn
Bi-directional relationship guidance - Power BI | Microsoft Learn

We hope this information helps you solve the issue. If you have more questions, please feel free to ask the Microsoft Fabric community.

Thank you.

[Irwan]

hello @dwhittaker1 

if you must do, many-to-many relationship is not bad in my opinion.

what you need to ensure is the unique value in column relationship when using many-to-many to prevent mismatch between two table.

in my case, i would create calculated column for concatenate two column in both table then do the many-to-many relationship in those two column.

for example: if you are using Part# as relationship then using many-to-many might lead into wrong value because there are multiple Part# with same description.

Hope this will help.

Thank you.