MDX count many-to-many dimension pairs

In SQL Server Analysis Services i created a cube to analyse sport games. The fact table contains information about a game like Date, HomeTeamId, AwayTeamId, HomeTeamResult, AwayTeamResult etc.

For the two teams (HomeTeamId and AwayTeamId) i created a many-to-many relationship as described here: https://learn.microsoft.com/en-us/sql/analysis-services/lesson-5-3-defining-a-many-to-many-relationship

Currently i can query the games played by team with this MDX query:

SELECT  { [Measures].[Game Count] } ON 0,
{[Team].[Team Id].[Team Id]} ON 1
FROM [Apps Swiss Ice Hockey]

this returns a result of:

+---------+------------+
| Team Id | Game Count |
+---------+------------+
|    1    |     27     |
|    2    |     21     |
|    3    |     34     |
+---------+------------+

so far so good. Now i want to create a query to analyze how many games where played for all possible team pairings. The result should look something like this:

+-----------+-----------+------------+
| Team 1 Id | Team 2 Id | Game Count |
+-----------+-----------+------------+
|     1     |     2     |      7     |
|     1     |     3     |     20     |
|     2     |     3     |     14     |
+-----------+-----------+------------+

I tried to get this result with crossjoining the team dimension like this:

SELECT  { [Measures].[Game Count] } ON 0,
{[Team].[Team Id].[Team Id]} * {[Team].[Team Id].[Team Id]} ON 1
FROM [Apps Swiss Ice Hockey]

but this results in an error:

The Team Id hierarchy is used more than once in the Crossjoin function.

Is it possible to achieve the described result with an MDX query?

Solution

You cannot crossjoin the same hierarchy, you can only create another hierarchy (the same as the existing one) of the same Team dimension and then you will be able to use crossjoin function.

Crossjoin(
    {[Team Dimension].[Team Hierarchy 1].members}
     ,{[Team Dimension].[Team Hierarchy 2].members}
    )