Forum Discussion

bfj4800's avatar
bfj4800
Frequent Visitor
6 years ago

Matchtable

I have a match table with 2 teams, a home team and a visiting team. The score for each team is also in the table.
What I would like to do is, that I can be able to filter (page filter) a visual table with all matches where a specified (chosen team) is eather home team or visiting team. I would also like to calculate, if the filtered team is the winner og looser, or if the match ended equal.

2 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi bfj4800 

    Im not sure how exactly do you want to calculate winner/looser/draw

    So, i have a solution, maybe not the best, but..

     

    first, i added index column to original table:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmcgNlCK1YkGs4yBGCRjBBZxArMgagzBIiA5E6iIiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Home = _t, HomeScores = _t, Visitors = _t, VisitorScores = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Home", type text}, {"HomeScores", Int64.Type}, {"Visitors", type text}, {"VisitorScores", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
    #"Added Index"

    then i dublicated it twice:

    i created table with index and team in match columns

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmcgNlCK1YkGs4yBGCRjBBZxArMgagzBIiA5E6iIiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Home = _t, HomeScores = _t, Visitors = _t, VisitorScores = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Home", type text}, {"HomeScores", Int64.Type}, {"Visitors", type text}, {"VisitorScores", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Index",{"Visitors", "Home", "Index"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Index"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Value", "Team"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"})
in
    #"Removed Columns"

    and i created table with index match, team name and calculated team result:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYmcgNlCK1YkGs4yBGCRjBBZxArMgagzBIiA5E6iIiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Home = _t, HomeScores = _t, Visitors = _t, VisitorScores = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Home", type text}, {"HomeScores", Int64.Type}, {"Visitors", type text}, {"VisitorScores", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "HomeResult", each if [HomeScores]>[VisitorScores] then 
Text.Combine({[Home],"|","W"})
else if [HomeScores]=[VisitorScores] then
Text.Combine({[Home],"|","D"})
else Text.Combine({[Home],"|","L"})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "VisitorResult", each if [HomeScores]>[VisitorScores] then 
Text.Combine({[Visitors],"|","L"})
else if [HomeScores]=[VisitorScores] then
Text.Combine({[Visitors],"|","D"})
else Text.Combine({[Visitors],"|","W"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Home", "HomeScores", "Visitors", "VisitorScores"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Index"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns1", "Value", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type text}, {"Value.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Value.1", "Team"}, {"Value.2", "Result"}})
in
    #"Renamed Columns"

    next i created calculated table in dax

    teamList = DISTINCT(matchTeams[Team])

    next i setted up relashionships

     

    and create visuals

     

     See attach