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!

MURTAZA's avatar
MURTAZA
Resolver I
5 years ago

Hi  Anonymous 

Your data model would be one to many relationship, and you can not bring the RELATED data to the one side of the relationship (it can be done only to Many side of the relationship)

The way around I can suggest is to create a one to one relationship by creating unique key in both tables using:
1. "Add new Column" and then use

Concatenate =

 

'Table competition'[id_competition] & "-" & 'Table competition'[id_competitor]

 

 Similarly in the other table

Concatenate =

 

'Table winners'[id_competition] & "-" & 'Table winners'[id_competitor]

 

Now create the relation between both concatenate columns.
3. Then using the RELATED function, you can bring in all the winners data.

For example, Goto Table Winners and create a new column using:

Average =

 

RELATED('Table competition'[average])
Similarly for other columns as well.