Matchtable

bfj4800 — Wed, 18 Sep 2019 08:55:06 GMT

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.

Re: Matchtable

az38 — Wed, 18 Sep 2019 19:43:38 GMT

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

Re: Matchtable

az38 — Wed, 18 Sep 2019 19:46:43 GMT

Hi bfj4800

My long long reply didn't saved (((

so, try pbix file in attach

topic Matchtable in DAX Commands and Tips

Matchtable

Re: Matchtable

Re: Matchtable