0

Error: Expression does not return multiple values

Hello all,

Happy new year. I am a newbie and I will appreciate any help solving my problem. 

I attach the data model for your convenience. In the Faculty table I run the formula 

sum(XrefSubjects.'Subject Allocation'[Year = "1"].'Points Earned')

and I get the error: Expression does not return multiple values: 'Subject Allocation' at line 1, column 38

What I want to do is to sum all the points for the subject that are taught by a faculty in Year = 1.

Best,
Bogac

13 replies

null
    • Bogachan_Celen
    • 3 yrs ago
    • Reported - view
    • Bogachan_Celen
    • 3 yrs ago
    • Reported - view

    Sorry for some reason the picture doesn't come through. 

    Subjects > Subject Allocation (Subjcts is the parent of Subject Allocation)

    Faculty (this has bunch of children)

    Subject Allocation <--- XrefSubject ---> Faculty

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    What types of value is 'Ordre d''inscription' and Year ?

    • Bogachan_Celen
    • 3 yrs ago
    • Reported - view

    Hi Jacques,

    Thank you. They're numbers but I tried everything, i.e. number(Year) = 1 etc.

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    I think to understood :
    XrefSubject is both field of 'Subject Allocation' and one table.

    If you want to make sum of the table, write  sum(select XrefSubjects.'Subject Allocation'[Year = "1"].'Points Earned')

    Else Ninox is right, the field XrefSubjects (of 'Subject Allocation' table) as only pointer to one record. You can't make sum of one record.

    The trouble is because Ninox put by default the same name for Table and field point to table. To clarify situation, the best way is change the name of field.

    • Bogachan_Celen
    • 3 yrs ago
    • Reported - view

    sum(select XrefSubjects.'Subject Allocation'[Year = "1"].'Points Earned') returns the same error. The part that I do not understand is concat(XrefSubjects.'Subject Allocation'[Year = "1"].'Points Earned') returns multiple values. How is that possible?

    • Bogachan_Celen
    • 3 yrs ago
    • Reported - view

    I also changed the names of the fields, the result is the same.

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    Could try with : sum(select 'Subject Allocation'[Year = "1"].'Points Earned') ?

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    oups : Could you try with : sum(select 'Subject Allocation'[Year = "1"].'Points Earned')

    • Bogachan_Celen
    • 3 yrs ago
    • Reported - view

    Jacques thanks so much for all the help!

    sum(select 'Subject Allocation'[Year = "1"].'Points Earned') works with one caveat it doesn't filter for an individual faculty member. It gives me all the subjects that are taught in 2001 by all faculty members.

    • Bogachan_Celen
    • 3 yrs ago
    • Reported - view

    The problem seems to originate from Year=1
    concat(XrefSubjects.'Subject Allocation'.'Points Earned') works fine and gives me all the points for all the years. When I say [Year = "1"] it fails.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    My take on the issue: when you add the filter, Ninox applies it to the derefencing of the "'Subject Allocation'" reference field only. This field is N:1, so dereferencig it does indeed not return multiple values, and a filter cannot be applied. You need to apply the filter to the dereferencing of the chain of references "XrefSubject.'Subject Allocation'". The normal syntax to indicate this is to put the chain into parentheses, so the expression becomes "sum((XrefSubject.'Subject Allocation')[Year=1].'Points Earned')". That expression is indeed accepted in the formula editor, but as soon as one hits "OK", Ninox removes the parentheses and flags the formula as incorrect. IHMO this is smelling like a bug. Fortunately, there is a workaround:

    let x := XrefSubject.'Subject Allocation';
    sum(x[Year = 1].'Points Earned')

    • Bogachan_Celen
    • 3 yrs ago
    • Reported - view

    This nails the problem indeed. Thanks so much Alain.

Login to reply

Content aside

  • 3 yrs agoLast active
  • 13Replies
  • 1225Views