Forum Discussion
Subsetting a table based on other table
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
Edited: I realised we need measures fr the correct result
If the data is as you have shown, all you need to do is create a one-to-many relationship between both tables by joining the Id competition fields:
You can the create a visual using the fields from the "Winner" table andthe following measures:
Average Time =
CALCULATE (
AVERAGE ( Competitions[average] ),
TREATAS (
VALUES ( Winners[id_competitor Winner] ),
Competitions[id_competitor]
)
)
Best Time =
CALCULATE (
MIN ( Competitions[best_time] ),
TREATAS (
VALUES ( Winners[id_competitor Winner] ),
Competitions[id_competitor]
)
)
If, however, you have a more complex data structure (competion by meeting etc) then you will need to change the model structure slightly.
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.
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)?
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!