Get value from Enumlist

[Yos]

Hello.
I have a table the table “Students”, and another table “GroupEvents” in which there are two relevant columns: The column “Group” contain lists of students names (from enum list input) and the other is called “Score” and contains a numeric value for each group.

Now, I want to create a new virtual column in the students’ table, with the sum of the scores per student.

I wrote the following expression:
sum(select(GroupEvents[Score],in([_thisrow].[StudentName],[Group])))
It keeps return zeros for every student, no matter how many times he has a score with a group.
I’m struggling with it for a few days and nothing. Can someone help me see what I’m missing?

Thanks!

[Pratyay_Rakshit]

Are you sure that the [Score] column is formatted as number and not as text?

[Yos]

SkrOYC:

If the EnumList called [Group] is on the table GroupEvents, it’s basetype Ref and is pointing to the Students table, try:

SUM(
  SELECT(
    GroupEvents[Score],
    IN(
      [_THISROW],
      [Group]
   

Yes. I made sure of it.

[shahappsheet]

So far I know, Enum returns text (default), so you can try to convert the text to number by using the function NUMBER().

[Yos]

Hi.
It is of type Number. please see my reply below.

[SkrOYC]

If the EnumList called [Group] is on the table GroupEvents, it’s basetype Ref and is pointing to the Students table, try:

SUM(
  SELECT(
    GroupEvents[Score],
    IN(
      [_THISROW],
      [Group]
    )
  )
)

[Yos]

Thanks, but the items inside each “group” recording are not the key from the table students (not the ID but the names. I guess this is where the problem begins.

[SkrOYC · Accepted solution]

Your enumlist should be basetype ref and point to the students table. Then the Label will be shown, I guess that the Name is the label in that table

[Yos]

I’ve just tested it again. The sumation without the condition works fine and returns a numeric value (sum of all the scores in the GroupEvents table. It is the condition which isn’t working. It looks like it can’t find studentName inside the enum list in the column group.
I’ve tried FIND(), IN(), CONTAINS() and nothing works. Is it because these expressions work only on type text and not on enum lists?
I guess I still need your help