Get Values from other Rows in the same Table.

[ebuttazzi]

First up, sorry if this was already asked and solved, i didn't manage to find anything on similar to what i want.

I have a table with a ID, a group and a Class, and i want a function that returns the id of the [only] other Class equal on the group
As the example, there are 10 itens per group, and 5 classes (that are always numbers from 1-5). I'm trying to manipulate the tables but i ultimatelly failed.

ID   Group   Class  ---What i Want---
1        1          1                    9
2        1          3                    7
3        1          5                    6
4        1          2                    10
5        1          4                    8
6        1          5                    3
7        1          3                    2
8        1          4                    5
9        1          1                    1
10      1          2                    4

Any help?

[v-frfei-msft · Accepted solution]

Hi @ebuttazzi,

We can create a calculated column based on your data.

Column = CALCULATE(SUM(Table1[ID]),FILTER(ALL(Table1),Table1[Class]=EARLIER(Table1[Class])))-Table1[ID]

Then we can get the result as you need.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/xbfnei6ws04nw9w/Get%20Values%20from%20other%20Rows%20in%20the%20same%20Tab...

Regards,Frank

[ebuttazzi]

Hey Frank, First, i would like to thank you for the solution, i was not aware of the existence of the EARLIER func.

Your solution would work great if not the fact that ALL(table) messes values up when there are more than one group. I manage to work aroud it using:

ID counterpart = CALCULATE(SUM(table[id]);FILTER(FILTER(table[group] = EARLIER(table[group]));table[class]=EARLIER(table[class]))) - table[id]

I just wondered if my solution for the group filtering (that works) is the most efficient.

Again, Thank you a lot for helping me out with this issue, i wish you the best.

Enzo