Forum Discussion

codeman71's avatar
codeman71
Frequent Visitor
5 years ago
Solved

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!

 

  • codeman71's avatar
    codeman71
    5 years ago

    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 IDVehicle NameFeature IDDisplay ID
    163064Vehicle A163665 
    163064Vehicle A163512 
    163064Vehicle A163519 
    163065Vehicle B163512 
    163073Vehicle C163616 
    163073Vehicle C163627 
    163073Vehicle C163665 
    163073Vehicle C163516 
    163064Vehicle A 163163
    163065Vehicle B 163167
    163073Vehicle C 163156
    163073Vehicle 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 IDVehicle NameFeature IDDisplay ID
    163064Vehicle A163665163163
    163064Vehicle A163512163163
    163064Vehicle A163519163163
    163065Vehicle B163512163167
    163073Vehicle C163616163156
    163073Vehicle C163627163156
    163073Vehicle C163665163156
    163073Vehicle C163516163156
    163073Vehicle C163616163167
    163073Vehicle C163627163167
    163073Vehicle C163665163167
    163073Vehicle C163516163167

     

    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_'s avatar
    onurbmiguel_
    Icon for Power Participant rankPower 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

     

    • codeman71's avatar
      codeman71
      Frequent Visitor

      Sample tables:

       

      Displays Table (Just lists all displays and their IDs):

      Display IDDisplay Name

      163167Square
      163163Rectangle
      163156Circle

       

      Displays to Features (Lists features that are on each display)

      Display IDDisplay NameFeature ID
      163167Square163585
      163167Square163516
      163163Rectangle163585
      163163Rectangle163638
      163163Rectangle163504
      163156Circle163664
      163156Circle163513
      163167Square163664

       

      Features (Lists all features and their names):

      Feature IDFeature Name
      163616Feature 1
      163638Feature 2
      163665Feature 3
      163658Feature 4
      163651Feature 5

       

      Vehicles (Lists features on the vehicles, and the displays on each vehicle)

      Vehicle IDVehicle NameFeature IDDisplay ID
      163064Vehicle A163665 
      163064Vehicle A163512 
      163064Vehicle A163519 
      163065Vehicle B163512 
      163073Vehicle C163616 
      163073Vehicle C163627 
      163073Vehicle C163665 
      163073Vehicle C163516 
      163064Vehicle A 163163
      163065Vehicle B 163167
      163073Vehicle C 163156
      163073Vehicle 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_'s avatar
        onurbmiguel_
        Icon for Power Participant rankPower 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's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion

    if you joined your display and displays to features tables into one table it might simpify your model.