How to build query and relationships

[pasit]

I want to create new table by using DAX or filter current data.

I have 2 tables TEAM that has 2 columns TeamName and TeamID.

Another table is GAMES. There are fields HomeGoals, AwayGoals, Gameday, HomeID and AwayID. HomeID and AwayID are joining GAMES table to TEAM table.

How can I create new resutset so that I get filtered fields HomeTeam, HomeGoals, AwayTeam, AwayGoals and Gameday as a table ?

I want to use the TeamName as a filter in TEAM table. So I need to be able to filter data so that if I select one team from TEAM table I should be able to list all games and results in a list so that it will show goals and also home and away team names. Any ideas how can I reach this ?

[Anonymous]

Hi  @pasit ,

Here are the steps you can follow：

1. Create measure.

Flag =
var _select=SELECTEDVALUE('Teams table'[TeamName])
var _id=MAXX(FILTER(ALL('Teams table'),'Teams table'[TeamName]=_select),[TeamID])
return
IF(
    MAX('Games table'[HomeID])=_id || MAX('Games table'[AwayID]) =_id,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[pasit]

I tried this, but somehow it did not work in my case.

My goal is to select a team and show selected teams home and away games and results. Only problem is that I have to create two relationships between TEAM and GAMES table for TeamID joined to HomeTeamID and AwayTeamID. 

Is there any workaround in DAX ... if I write query with SQL it is very easy

[Anonymous]

Hi  @pasit ,

According to your description, the two tables can be related by [HomeID], you can directly select the TeamName of the Team table as a slicer, and when the slicer selects a certain TeamName, it will automatically display the corresponding data of the GAMES table as a connection with the [HomeID] column.

This is the data I created:

Team:

Games：

Result:

If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pasit]

Actually what I mean ...

Teams table has following data

TeamID TeamName
1           Team1
2           Team2
3           Team3
4           Team4

Games table has following data

HomeID AwayID HomeGoals AwayGoals
1            3           1                  1
3            2           3                  1
2            4           0                  1
3            1           2                  1
4            1           1                  2

And in the visuals

I have slicer of each teams from team table and if I for example choose Team2 my table visual shows results from team and games table like

Home Away         Homegoals Awaygoals
Team3 Team2       3                1
Team2 Team4       0                1

So I want to show selected team home and awaygames and those results. Team table TeamID must join both HomeID and AwayID in games table