Forum Discussion
Trouble with filter involving multiple tables
I'm hoping I'm missing something rudimentary on this one.
I have several tables relating Features, and the Vehicles and Displays that those features support. I also have a table showing what displays each vehicle supports:
I'd like to create a Matrix visualization to produce something like this:
This is my matrix setup:
And my measure:
Feature Exists =
var _Feature = SELECTEDVALUE(Features[Feature ID])
var _Display = SELECTEDVALUE(Displays[Display ID])
return
CALCULATE(
IF(_Feature in VALUES('Displays to Features'[Feature ID])
,"X"
),
FILTER('Displays to Features',
'Displays to Features'[Display ID] = _Display
),
)
Without the filter at the end, the Matrix will show me what Features are on what vehicle, like this, with the Displays row blank:
And with the Filter, the Matrix is completely empty. Do I need to do anything special because I'm using the selected value from one filter path to filter another related table?
Thank you!
Thank you very much for taking the time to reply! I was able to use a combination of your thinking and update my tables a bit. The problem was with this table:
Vehicles (Lists features on the vehicles, and the displays on each vehicle)
Vehicle ID Vehicle Name Feature ID Display ID 163064 Vehicle A 163665 163064 Vehicle A 163512 163064 Vehicle A 163519 163065 Vehicle B 163512 163073 Vehicle C 163616 163073 Vehicle C 163627 163073 Vehicle C 163665 163073 Vehicle C 163516 163064 Vehicle A 163163 163065 Vehicle B 163167 163073 Vehicle C 163156 163073 Vehicle C 163167 I didn't have a linkage between Vehicle to Display and Feature. I was able to modify my data a bit to give me the data like this:
Vehicle ID Vehicle Name Feature ID Display ID 163064 Vehicle A 163665 163163 163064 Vehicle A 163512 163163 163064 Vehicle A 163519 163163 163065 Vehicle B 163512 163167 163073 Vehicle C 163616 163156 163073 Vehicle C 163627 163156 163073 Vehicle C 163665 163156 163073 Vehicle C 163516 163156 163073 Vehicle C 163616 163167 163073 Vehicle C 163627 163167 163073 Vehicle C 163665 163167 163073 Vehicle C 163516 163167 This way my filtering can now pass through the Vehicles table correctly to Displays.
I'm not sure that I'd be able to fully use what you had proposed. I think I would lose the linkage between what Features are supported by what Displays. Because not all Features are supported by all Displays on a vehicle.
5 Replies
- onurbmiguel_
Power Participant
Hello
Can you share some lines of each table.
Way do you need table "Display to Features" ? In "Vehicle" tabel you have Display ID and Feature ID.
Have you analyse the direction of each connection?
I will waiting for your answer
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Best Regards
BC- codeman71Frequent Visitor
Sample tables:
Displays Table (Just lists all displays and their IDs):
Display IDDisplay Name
163167 Square 163163 Rectangle 163156 Circle Displays to Features (Lists features that are on each display)
Display ID Display Name Feature ID 163167 Square 163585 163167 Square 163516 163163 Rectangle 163585 163163 Rectangle 163638 163163 Rectangle 163504 163156 Circle 163664 163156 Circle 163513 163167 Square 163664 Features (Lists all features and their names):
Feature ID Feature Name 163616 Feature 1 163638 Feature 2 163665 Feature 3 163658 Feature 4 163651 Feature 5 Vehicles (Lists features on the vehicles, and the displays on each vehicle)
Vehicle ID Vehicle Name Feature ID Display ID 163064 Vehicle A 163665 163064 Vehicle A 163512 163064 Vehicle A 163519 163065 Vehicle B 163512 163073 Vehicle C 163616 163073 Vehicle C 163627 163073 Vehicle C 163665 163073 Vehicle C 163516 163064 Vehicle A 163163 163065 Vehicle B 163167 163073 Vehicle C 163156 163073 Vehicle C 163167 To summarize:
- A Vehicle may have one or more displays.
- A Vehicle will support a subset of features.
- Each Display supports multiple features.
- However, the instance of a display on a vehicle is where both of these two intersect. (A feature must be supported by both the Vehicle and the Display in order for it to be present)
- onurbmiguel_
Power Participant
Hello,
Sorry the delay.
My approach:
- in my opinion you need to reset the way you have the information in tables, i suggest the following way:in table "FACT_Vehicles" you need to correctly cross-reference the information between the display and the feature by vehicle.
After you creat a simple measure:
# Vehicles = DISTINCTCOUNT('FACT_Vehicles Info'[Vehicle ID])I send you the link for download my approch
https://1drv.ms/u/s!AkcWVrMFkXs1hvxpwu_F2x-43Nc6uw?e=IdRZGH
Any question ask.
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Best Regards
BC
- vanessafvg
Community Champion
if you joined your display and displays to features tables into one table it might simpify your model.