Filter Data | Between dimension tables

[jayanthan]

Hi there,

I have an issue of filtering data between master tables. For an instance, there is a relationship between sales, organization table, and product table. when I filter the organization, the chart gets filtered. however, the product does not get filtered based on organisation as there is no direct relationship between the organisation and product table. When I try to create a relationship between organisation and product table following error pops up. Please advise

Regards,

Jayanthan

[V-pazhen-msft]

@jayanthan 

You need a many side table to remove the ambiguity. It seems all tables are having unique values, in this case you could just merge the 3 tables into 1 table. 

Organisation Id, Product ID, Name, Value, Product name. 

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

[jayanthan]

Hi @V-pazhen-msft ,

Like the sales fact table,  the power bi file has purchase, profit, and many more table related products and organisation. Hence, it's not the right way to merge fact tables with dimension tables.

[ibarrau]

Hi. I would suggest you to keep the model with single relationships and follow this practise:

https://blog.ladataweb.com.ar/post/185907335292/datamodeling-sincronizar-filtros-entre

You can get the dimension filtered by each other with their matches inside a fact. You need a measure to filter it. The post contains a more detail explanation.

You can also check this user that had the same issue:

https://community.powerbi.com/t5/Desktop/Filtre-between-dimension-doesnt-work-with-a-single-column/m...

Regards,

[DataMozart]

Hi jayanthan,

Can you please share a screenshot of the model view from the PBI Desktop? I've tried to mimic your data model and PBI automatically created bidirectional relationships between the entities (please have a look at the screenshot attached), without any error messages popping up.

[jayanthan]

Hi @DataMozart ,

It would be difficult to analyze using the data model as the power bi has many table. Please find the requested screenshot below: