Matrix data

[ezequiel]

Hi,

I'm building a fixture-like matrix for a soccer tournament with 10 teams. All plays with each other, so I'll have a 10x10 matrix with different combinations of results, except for the accross diagonal line (as Team 1 vs Team 1 will not generate a result). I though about building a simple table in excel with this structure:

When I build the matrix in PowerBI, adding "Relation1" as values, I'm getting this:

I'm facing 2 issues:

1. It is only showing the results from the rows point of view, for example, it is populating the result of AB but not BA. I'm not getting the reason of this

2. is it possible to show the opposite result that I have in my field called "Relation2" if I see this from one team or the other? On the example above the value within BA should be 1-4

I appreciate your help

Ezequiel

[Anonymous · Accepted solution]

Hi @ezequiel,

It is possible to achieve your requirement, please refer to below steps:

1. Create two calculated tables to store summarize distinct team names from original table.(not exist relationship)

T1 =
DISTINCT ( UNION ( VALUES ( Sheet1[Team1] ), VALUES ( Sheet1[Team2] ) ) )

T2 =
DISTINCT ( UNION ( VALUES ( Sheet1[Team2] ), VALUES ( Sheet1[Team1] ) ) )

2. Write a measure to get row label and column label to compare and look up result from original table.

Result = 
VAR lookup1 =
    LOOKUPVALUE (
        Sheet1[Relation1],
        Sheet1[Team1], SELECTEDVALUE ( 'T1'[Team1] ),
        Sheet1[Team2], SELECTEDVALUE ( 'T2'[Team2] )
    )
VAR lookup2 =
    LOOKUPVALUE (
        Sheet1[Relation2],
        Sheet1[Team1], SELECTEDVALUE ( 'T2'[Team2] ),
        Sheet1[Team2], SELECTEDVALUE ( 'T1'[Team1] )
    )
RETURN
    IF (
        lookup1 = BLANK ()
            && lookup2 = BLANK (),
        IF ( SELECTEDVALUE ( T1[Team1] ) = SELECTEDVALUE ( T2[Team2] ), "NA" ),
        IF ( lookup1 <> BLANK (), lookup1, lookup2 )
    )

3. Use T1, T2 and above measure to create matrix visual.

Regards,

Xiaoxin Sheng