Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Subsetting a table based on other table

Hi, newbie here. I've been trying and researching how to do this all weekend, but nothing worked for me yet. Any help would be appreciated. Basically, I have two tables: The first one is called "...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    I did it!! I just used the FILTER function like "NewTable = FILTER('competition', 'competition'[id_competition] = RELATED('winners'[id_competition]) && 'competition'[id_competitor] = RELATED('winners'[id_competitor]))"

    Thanks again for the help!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi! Thanks for helping. I do indeed have a more complex data structure. In fact, my database is already normalized. The columns "id_competition" are foreign keys to a table that has "id_competition, name_competition, country, etc"; The columns "id_competitor" are also foreign keys. They reference a table that has "id_competitor, name_competitor, age, etc".

 

The two tables I want to "join" are not directly connected. I think there must be a way to compare the two columns on both of the tables, like "filter the rows from table competition if id_competition and id_competitor from table competition are equal to id_competition and id_competitor from table winners".

 

Let me know if I can provide more details.

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

Anonymous 

You can create a virtual relationship using TREATAS (see my edited post above) to filter unrelated fields. Can you show the model view of the relevant tables (both dimension and fact tables)?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I did it!! I just used the FILTER function like "NewTable = FILTER('competition', 'competition'[id_competition] = RELATED('winners'[id_competition]) && 'competition'[id_competitor] = RELATED('winners'[id_competitor]))"

    Thanks again for the help!