Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
Regards,Frank
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.
Regards,Frank
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |