Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi community,
I have de following dataset:
id | step | val1 | val2 |
101 | 1 | 5 | 5 |
101 | 2 | 8 | 5 |
101 | 3 | 10 | |
101 | 4 | 3 | 8 |
101 | 5 | 15 | |
102 | 1 | 9 | |
102 | 2 | 2 | 7 |
102 | 3 | 5 | |
102 | 4 | 7 | 13 |
102 | 5 | 8 |
I need get a value of val2 where val1 is not null, for each ID, respecting the step. In example above, I need value 8 for ID 101 and 13 for ID 102. I tried use lastnoblank in a matrix with ID in rows but does not worked.
Basically, the result that I need in this example, is the matrix below:
ID | Val1 | Val2 | Diff |
101 | 3 | 8 | 5 |
102 | 7 | 13 | 6 |
Anybody help me in this case?
Solved! Go to Solution.
Here is a measure expression that calculates your different directly. If you want to return Val1 and Val2, just return those variables instead of the difference.
Difference =
VAR __maxstepnotnull =
CALCULATE ( MAX ( val[step] ), NOT ( ISBLANK ( val[val1] ) ) )
VAR __val1 =
CALCULATE ( MIN ( val[val1] ), val[step] = __maxstepnotnull )
VAR __val2 =
CALCULATE ( MIN ( val[val2] ), val[step] = __maxstepnotnull )
RETURN
__val2 - __val1
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is a measure expression that calculates your different directly. If you want to return Val1 and Val2, just return those variables instead of the difference.
Difference =
VAR __maxstepnotnull =
CALCULATE ( MAX ( val[step] ), NOT ( ISBLANK ( val[val1] ) ) )
VAR __val1 =
CALCULATE ( MIN ( val[val1] ), val[step] = __maxstepnotnull )
VAR __val2 =
CALCULATE ( MIN ( val[val2] ), val[step] = __maxstepnotnull )
RETURN
__val2 - __val1
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
34 | |
14 | |
12 | |
11 | |
10 |
User | Count |
---|---|
61 | |
20 | |
19 | |
15 | |
11 |