Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
JacobMotu
Frequent Visitor

Filter unrelated table

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!
2023-07-04 10_54_33-model-ambiguity.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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

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))

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.