Generate table multiple columns

[titanconsulting]

I'm struggling to deal with data that is coming in from an external API.  I'd like to do some analysis on College Football - but the data for the games in coming in with a home and an away team.  The front end is going to be driven by team - so I'll need a seperate record for each team so I can analyze no matter if they are home or away.

id                  school

2000	Abilene Christian
2001	Adams State
2003	Adrian
2005	Air Force
2006	Akron
333	Alabama
2010	Alabama A&M
...	...

Year      id                 season week season_type home_id  home_team                  home_points  away_team      away_points

2022	401426532	2022	1	regular	98	Western Kentucky	38	Austin Peay	27
2022	401405059	2022	1	regular	77	Northwestern	31	Nebraska	28
2022	401439512	2022	1	regular	125762	Florida Memorial University	39	Edward Waters	34
2022	401413213	2022	1	regular	55	Jacksonville State	42	Stephen F. Austin	17
2022	401413256	2022	1	regular	2439	UNLV	52	Idaho State	21
2022	401404146	2022	1	regular	328	Utah State	31	Connecticut	20

So I'd like to see how many times Western Kentucky won reguardless if they are home or away.  Current thought is create a new table, combining a Years table, Season Base (week + season_type) and teams table to get a record for every game played by team.  Teams have bye weeks - so won't have a game each week.   Totally open to hearing this isn't the right approach though.  I think ideally this is a Power Query answer - but I'll deal with dax.

Here is my actual pbix file with the data in it

https://drive.google.com/file/d/15FeBnODGLPVy_RL_OX58ZhN4Nn4Tze9K/view?usp=sharing

Appreciate any help that can be given here.

[tackytechtom · Accepted solution]

Hi @titanconsulting ,

I think this is mostly a modelling quetion.

How about creating two relationships from your teams table ("teams dimension") to your results table ("results fact"). Then you could possibly create a measure with the following logic:

DIVIDE ( 

CALCULATE ( COUNT ( home_team ), home_points > away_points, USERELATIONSHIP ( id, home_team ) ) +

CALCULATE ( COUNT ( away_team  ), home_points < away_points, USERELATIONSHIP ( id, away_team ) ), 

CALCULATE ( COUNT ( home_team ), USERELATIONSHIP ( id, home_team ) ) +

 CALCULATE ( COUNT ( away_team ), USERELATIONSHIP ( id, away_team ) ) )

Note, the syntax might be wrong, but I hope this gives you an idea anyway.

Let me know if this helps and if not feel free to share your pbix file and I can have a look into it.

Thanks,

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[titanconsulting]

Accepting this as solution because it gave me some ideas to go forward with.  Don't know if my solution is the most efficient...but it works.  With the amount of data I've got, it'll work.

1. Duplicated the games table - Labeled one as Home Game and the other as Away Game.
2. Merged Team table to Home Game into new table.  Same for Away Game.
3. Appended the two tables into a single table - Total Games.

It does work - this is my personal analysis so I not overly concerned about performance especially with the amount of data I've got.

Thanks for the help!

[Greg_Deckler]

@titanconsulting Maybe a combination of the two, you could start with this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFNa4QwEIb/SvC8FBMTXY+ldKFfUljsHsRDqkMN2liSuEv/fWfcsgcV8vHmTeaZYVJVUbTbGvWuikQsBGoZcylSlYj/M8fp4GsatMv3qE/gAzjLXsCGqel/0UrIv598MJa9gyZLZEtmrGKVr5lZhroYXeguVzDx6L6AT6d9rwm2X8KSXPGNArlQWUreYRidaTV7g28UemClNWdw3oS53hyXx/aiXctOGpN6MuUyCU8EjlUSpVA/66b3oz2bYQB2DAihIHp5DPDTgWWHO3ZtCUWvuoFgla7ZQs61lcXrB26Kbp9a3Y23FIKv+iq53CAlgj6lDLq7xc5tfRithSaYZgqEi6O6/gM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, id = _t, season = _t, season_type = _t, home_team = _t, home_points = _t, away_team = _t, away_points = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"id", Int64.Type}, {"season", Int64.Type}, {"season_type", type text}, {"home_team", type text}, {"home_points", Int64.Type}, {"away_team", type text}, {"away_points", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Year] = 2022)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"id", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Year", "id", "season", "season_type", "home_points", "away_points"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Team"}, {"Attribute", "HomeAway"}})
in
    #"Renamed Columns"

and then a DAX measure like this:

Wins = 
    VAR __Table = ADDCOLUMNS('Table',"PointDiff",[home_points] - [away_points])
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "Win",
            SWITCH(TRUE(),
            [HomeAway] = "home_team" && [PointDiff] > 0,1,
            [HomeAway] = "away_team" && [PointDiff] < 0,1,
            0
            )
        )
RETURN
    SUMX(__Table1,[Win])