topic Re: Scoring from 2 tables and Dickinson system in DAX Commands and Tips

Scoring from 2 tables and Dickinson system

Pan_Forex — Fri, 13 Jan 2023 15:05:53 GMT

Hello guys, I wonder if it is possible to create functions and tables that will work like this. I currently have 3 tables. One with the players and their individual ratings. The other is with the games and the scores of the individual games. The third table is the calendar.

Date	id	player	team	won
13.01.2023	90000	A	0	TRUE
13.01.2023	90000	B	1	FALSE
13.01.2023	90000	C	0	TRUE
13.01.2023	90000	D	0	TRUE
13.01.2023	90000	E	1	FLASE
13.01.2023	90000	F	0	TRUE

Player	Score
A	6
B	5
C	6
D	5,5
E	5,3
F	7

I would like it to create a new column "Dickinson" in 1 table. The Dickinson system awards 30 points for a victory over a strong team, and 20 for victory over a weak team. Defeats count half as much as victories 15 pts vs. strong team, 10 pts vs. weak team. Whether a team is inferior is determined by the average of the team in the second table.

The result for the above example: Team 0 avg=5,83, Team 1 avg=5,76 Team 1 < Team 0

Date	id	player	team	won	Dickinson
13.01.2023	90000	A	0	TRUE	20
13.01.2023	90000	B	1	FALSE	15
13.01.2023	90000	C	0	TRUE	20
13.01.2023	90000	D	0	TRUE	20
13.01.2023	90000	E	1	FLASE	15
13.01.2023	90000	F	1	TRUE	20

Re: Scoring from 2 tables and Dickinson system

TomasAndersson — Fri, 13 Jan 2023 14:36:48 GMT

Hi!
I'm not completely sure of your questions. I don't get 5.6 and 6 as averages for team 0 and 1 when I look at your table and am not sure if there can be more than two teams and how the data looks in that case.

Here is an example code that should work in the example you gave, maybe it can help you on the way a bit:

Dicksinon = VAR __ThisTeamRating = CALCULATE(average(Players[Score]), RELATED(Players[Player]) = Results[player]) VAR __OpponentTeamRating = CALCULATE(average(Players[Score]), RELATED(Players[Player]) <> Results[player]) VAR __OpponentStrength = if(__OpponentTeamRating >= __ThisTeamRating, "Strong", "Weak") VAR __ScoreAwarded = IF(__OpponentStrength = "Strong",30,20) return if(Results[won] = "TRUE", __ScoreAwarded, __ScoreAwarded / 2)

Re: Scoring from 2 tables and Dickinson system

Pan_Forex — Fri, 13 Jan 2023 15:13:51 GMT

TomasAndersson Yes you are right, I have already corrected. There are always only two teams 0 and 1. Thank you very much for your help. It seems to me that there is a mistake somewhere because comparing the value of Text with a number is impossible. Still missing is the condition of averages for team 0 and 1 to be counted only for games with the same ID.

Re: Scoring from 2 tables and Dickinson system

TomasAndersson — Fri, 13 Jan 2023 15:45:50 GMT

I missed to add a condition so that only the team's average score is only calculated from players that belong to that team. I also added a similar to only look at players that were in the current game as well.

Dicksinon = VAR __ThisTeamRating = CALCULATE(average(Players[Score]), RELATED(Players[Player]) = Results[player], Results[team] = EARLIER(Results[team]), Results[id] = EARLIER(Results[id]) ) VAR __OpponentTeamRating = CALCULATE(average(Players[Score]), RELATED(Players[Player]) <> Results[player], Results[team] = EARLIER(Results[team]), Results[id] = EARLIER(Results[id]) ) VAR __OpponentStrength = if(__OpponentTeamRating >= __ThisTeamRating, "Strong", "Weak") VAR __ScoreAwarded = IF(__OpponentStrength = "Strong",30,20) return if(Results[won] = "TRUE", __ScoreAwarded, __ScoreAwarded / 2)

When it comes to the Text/Value error, let me know where that error occurs. This works with the data I'm using so it's probably a data type of some column that's different for you and me.

Re: Scoring from 2 tables and Dickinson system

Pan_Forex — Fri, 13 Jan 2023 16:57:39 GMT

Thank you so much for your time and work!

Re: Scoring from 2 tables and Dickinson system

Pan_Forex — Sat, 14 Jan 2023 14:18:23 GMT

TomasAndersson Hello, currently the function returns only the values 30 and 15. It should still return 20 and 10. If the inferior team won then 30 points for the winner and 10 for the loser. If the better team won then 20 points for the winner and 15 for the loser. I tried to add conditions to the if function and unfortunately it does not work.

Re: Scoring from 2 tables and Dickinson system

TomasAndersson — Sun, 15 Jan 2023 16:13:49 GMT

Hi, I think I did something wrong with calculating the team average score. It didn't calculate the average for the team as a whole, only for individual players.

Dicksinon = VAR __ThisTeamRating = CALCULATE(AVERAGEX(Results,RELATED(Players[Score])), FILTER(ALL(Results), Results[team] = EARLIER(Results[team]) && Results[id] = EARLIER(Results[id]) ) ) VAR __OpponentTeamRating = CALCULATE(AVERAGEX(Results,RELATED(Players[Score])), FILTER(ALL(Results), Results[team] <> EARLIER(Results[team]) && Results[id] = EARLIER(Results[id]) ) ) VAR __OpponentStrength = if(__OpponentTeamRating >= __ThisTeamRating, "Strong", "Weak") VAR __ScoreAwarded = IF(__OpponentStrength = "Strong",30,20) return if(Results[won] = "TRUE", __ScoreAwarded, __ScoreAwarded / 2)

Now it seems to return either 30 and 10 or 20 and 15: