Filter unrelated table

[JacobMotu]

Hi community,

This is my PBIX file.

I import data from the Access database which is vast and quite complex (15-20 tables). I simplified it to the core (picture below). There is an ambiguous relationship between the tables in regard to Variety. Power BI deactivates one of them so it can work. In the way in the picture, I can filter through Crop/Variety, but I can see all Varieties when using a slicer (no relationship between Variety and Crop). If I deactivate relationships between Variety and detail tables, filtering does not work, but I can see only Varieties related to a particular Crop as it should be. In Access db, there are actually both relationships activated and it works just fine. Is there any way how to make it work fully, so I can filter but see only related Varieties to specific Crops?
Thanks!

I'm looking into USERELATIONSHIP function to activate the relationship for the filter, but have no idea how to apply it to my problem.

[amitchandak · Accepted solution]

@JacobMotu , You can have measure like

calculate(countrows(tabvarities), filter(tabvarities, tabvarities[crop] in values(tblcrop[crop]) ) )

refer

How to filter the slicer of a disconnected table: https://youtu.be/cV5WfaQt6C8

[JacobMotu]

Thanks for the advice. Your formula does not work in my model, but I used the solution from the video. The most irritating fact for me was that I saw the video before I even asked for help here, but I made a mistake when creating the formula so I though it is not working 🙂

ControlCrop = VAR _tab = SUMMARIZE(ALLSELECTED(tblCrop), tblCrop[CropID])
RETURN
COUNTROWS(FILTER(tblVarieties, tblVarieties[Crop] in _tab))