Forum Discussion

titanconsulting's avatar
titanconsulting
Frequent Visitor
4 years ago
Solved

Generate table multiple columns

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...
Need Help
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    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/

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

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])