Power BI Desktop filtering - no relationship detected between 2 linked tables

[AndreeaPopescu]

Hello! I need your urgent help on a situation i have in PBI Desktop. 

I have the following model. The fact table BOM contains the list of products with its ingredients. The dimension table "PRODUCTS" contains the list of products (1 row per product). Then, the other dimension "Product nutrients" contains the products with its nutrients (called INSPECTION TYPE). We have 4 possible INSPECTION TYPES : FAT, PROTEIN, WATER and TS. one product can have up to 4 nutrients. 

My issue is that even though all 3 tables have the common key "MATERIAL" meaning the product code (even if the names of the columns seem a bit different), when i create a visual table where i try to add BOM [MATERIAL], PRODUCT[MATERIAL] and PRODUCT_NUTRIENTS[MATERIAL], there is no relationship detected and i get an error.

What i need for my project is to be able to add a page filter in my PBI report where i can filter by nutrients. i have 4 dashboard pages dedicated for each nutrient so i want to be able to filter each page by nutrient. At the moment i cannot do that because of this relationship issues.

My question:

if i cannot alter the relationships (maybe this error is based on the conflicting directions of the relationships, i would like maybe to filter using a dax measure (i think i need to use countrows or smth) but i m not sure how to proceed.

Also, if you have any other suggestion for the relashionship situation, i m open to all ideas.

the visual tables work 2 by 2, but when adding all the tables i get the error:

THANK you for your help!

[Anonymous]

Hi @AndreeaPopescu ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DataInsights]

@AndreeaPopescu,

The cross-filter direction between Product and Product_nutrients is the reason filtering on Product_nutrients doesn't filter the BOM table (the relationship arrow points from Product to Product_nutrients). You can override that by making the relationship bidirectional, but that's not recommended (can cause issues in semantic models). I recommend using a star schema. You can merge Product_nutrients into Product, resulting in one dimension table. This will simplify your model and enable filtering on any Product field.

Another option is the CROSSFILTER function that is available within CALCULATE. It allows you to specify the cross-filter direction within the scope of the measure.