Forum Discussion

Helper III

9 years ago

Solved

Help with relatedtable function

Hello. I'm tryiing to find

=SUMX(Filter(RELATEDTABLE('Work Orders1'),'Work Orders1'[WO Type]="Cashout"),'Work Orders1'[WO Type]="Cashout")

v-yulgu-msft
9 years ago
Hi, ammartino44

From your formula, the data type of column 'Work Orders1'[WO Type] is text, while SUMX function evaluates to numbers or dates and cannot work with values of type String. Beside, the correct syntax of SUMX is:
SUMX(<table>,<expression>)

In your scenario, the formula might be like :
=SUMX(FILTER(RELATEDTABLE('Work Orders1'),'Work Orders1'[WO Type]="Cashout"),ColumnName)

Best regards,
Yuliana Gu

3 Replies

GilbertQ
Super User
9 years ago
Hi there

I know this might be a simple question, but have you created the relationship between your tables?

Also when using the related table, from memory you can only reference the RelatedTable on the many side of the relationship (NOTE: This is off the top of my head, it might be the other way around!)
Anonymous
Not applicable
9 years ago
Gonna need more info on this one but... your formula does "look weird". The general form of SUMX is:
=SUMX(Table1, Expression)

It walks each row of the Table1, and evaluate the Expression... then adds the results together.

Your expression is : 'Work Orders1'[WO Type]="Cashout"

That seems like a weird expression!
v-yulgu-msft
Microsoft Employee
9 years ago
Hi, ammartino44

From your formula, the data type of column 'Work Orders1'[WO Type] is text, while SUMX function evaluates to numbers or dates and cannot work with values of type String. Beside, the correct syntax of SUMX is:
SUMX(<table>,<expression>)

In your scenario, the formula might be like :
=SUMX(FILTER(RELATEDTABLE('Work Orders1'),'Work Orders1'[WO Type]="Cashout"),ColumnName)

Best regards,
Yuliana Gu