Subsetting a table based on other table

[Anonymous]

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:

1. The first one is called "competition" and has the columns id_competition, id_competitor, and other types of data about how the competitor performed in the competition;

2. The second table is called "winners", and have only the columns id_competition and id_competitor, representing the competitor that won each competition.

1 - Table competition:

id_competition	id_competitor	best_time	average	etc
1	1	90	85	...
1	2	96	90	...
1	3	102	110	...
2	4	59	63	...
2	5	61	60	...
2	6	54	51	...
2	1	72	81	...
3	4	125	114	...
3	6	120	120	...
3	7	122	117	...

2 - Table winners:

id_competition	id_competitor
1	1
2	4
3	6

I want to create a new table with all the rows and columns from the table competition where the values of id_competition and id_competitor match those of the table winners. In other words, I just want to get the data of the winners of each competition from the table competitions.

I know how to do this using the power query editor, but I want to do it using DAX. In power query this is a simple "merge query as new", I think.

Result expected:

id_competition	id_competitor	best_time	average	etc
1	1	90	85	...
2	4	59	63	...
3	6	120	120	...

Thanks in advance.

[MURTAZA]

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.

[PaulDBrown]

@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.

[Anonymous]

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]

@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 · Accepted solution]

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!