Forum Discussion

Ormy269's avatar
Ormy269
Frequent Visitor
11 months ago
Solved

Unique Values Across Two Columns Using SUMMARIZE

Hi   I have a table of football fixtures ('All Results') which contains a home team, away team and the number of goals scored by each, see below: The full data set is much bigger than that, ...
Need Help
Shahid12523's avatar
Shahid12523
Community Champion
11 months ago

Team Summary =
SUMMARIZE (
UNION (
SELECTCOLUMNS ( 'All Results', "Team", [HomeTeam], "IsHome", 1, "GoalsFor", [Home], "GoalsAgainst", [Away] ),
SELECTCOLUMNS ( 'All Results', "Team", [AwayTeam], "IsHome", 0, "GoalsFor", [Away], "GoalsAgainst", [Home] )
),
[Team],
"Home Games", CALCULATE ( COUNTROWS ( 'All Results' ), [IsHome] = 1 ),
"Away Games", CALCULATE ( COUNTROWS ( 'All Results' ), [IsHome] = 0 ),
"Home Goals Scored", CALCULATE ( SUM ( [GoalsFor] ), [IsHome] = 1 ),
"Away Goals Scored", CALCULATE ( SUM ( [GoalsFor] ), [IsHome] = 0 ),
"Goals Conceded Home", CALCULATE ( SUM ( [GoalsAgainst] ), [IsHome] = 1 ),
"Goals Conceded Away", CALCULATE ( SUM ( [GoalsAgainst] ), [IsHome] = 0 )
)


This creates one summary table with:

Home/Away Games

Goals scored (home/away)

Goals conceded (home/away)

  • Ormy269's avatar
    Ormy269
    Frequent Visitor
    11 months ago

    Hi Shahid12523,

     

    I think this is getting close, but the DAX returs some errors. Hovering the mouse over IsHome, GoalsFor and GoalsAgainst shows an error of "Cannot find Name". Also, a second error of "Parameter is not the correct type" for GoalsFor and GoalsAgainst. Does there need to be a table reference here?