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.
Hello How can I get the first negative number for each ID and Task before the first positive number like in the following table?
ID: 0 TASK: 1 First negative number before first positive number = -20
ID: 11 TASK: 3 First negative number before first positive number = -10
ID: 5 TASK: 2 First negative number before first positive number = -8
ID | TASK | PROD |
0 | 1 | -100 |
0 | 1 | -50 |
0 | 1 | -20 |
0 | 1 | 10 |
0 | 1 | 40 |
0 | 1 | 100 |
11 | 3 | -50 |
11 | 3 | -10 |
11 | 3 | 20 |
11 | 3 | -2 |
11 | 3 | -10 |
11 | 3 | 10 |
5 | 2 | -50 |
5 | 2 | -8 |
5 | 2 | 20 |
5 | 2 | -5 |
5 | 2 | 30 |
Solved! Go to Solution.
Hi @OscarSuarez10 ,
You will need to go to Power Query and add a index column.
Then
Get pos =
VAR _getfirstpos =
CALCULATE (
MIN ( Neg[Index] ),
FILTER ( ALLEXCEPT ( Neg, Neg[ID], Neg[TASK] ), Neg[PROD] > 0 )
)
VAR _getfirstneg =
CALCULATE (
MAX ( Neg[PROD] ),
FILTER ( ALLEXCEPT ( Neg, Neg[ID], Neg[TASK] ), Neg[Index] < _getfirstpos )
)
RETURN
_getfirstneg
to get this
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @OscarSuarez10 ,
You will need to go to Power Query and add a index column.
Then
Get pos =
VAR _getfirstpos =
CALCULATE (
MIN ( Neg[Index] ),
FILTER ( ALLEXCEPT ( Neg, Neg[ID], Neg[TASK] ), Neg[PROD] > 0 )
)
VAR _getfirstneg =
CALCULATE (
MAX ( Neg[PROD] ),
FILTER ( ALLEXCEPT ( Neg, Neg[ID], Neg[TASK] ), Neg[Index] < _getfirstpos )
)
RETURN
_getfirstneg
to get this
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
Hi @OscarSuarez10 ,
When you say the first negative number, are you looking for the highest negative number?
Nathaniel
Proud to be a Super User!
Hi @Nathaniel_C no, I am looking for the first negative number that appears immediatly before the first positive number according to the Product column
Ok, I figured that. How does that solution work for you? Nathaniel
Proud to be a Super User!
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 |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |