Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |