Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello everyone!
I'm struggling with this calculation.
My goal is to get the sum off the value, but it has a twist:
1) I want to subtract the 1s step to the next one with a value, but in the same project.
For the project 17651, i want to subtract the 1st step in the 3rd(because it is the next with a value, 2nd step is blank)
For the project 45521, it should subtract step 1 to step 2.
Project |Step | | Value | Goal
|
Can you help me, please?
Solved! Go to Solution.
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Value measure: =
SUM( Data[Value] )
Goal measure: =
VAR _currentproject =
MAX ( Data[Project] )
VAR _minstepaftersteptwo =
MINX (
FILTER (
ALL ( Data ),
Data[Project] = _currentproject
&& Data[Step] > 1
&& Data[Value] <> BLANK ()
),
Data[Step]
)
VAR _steponevalue =
CALCULATE ( [Value measure:], Data[Step] = 1 )
RETURN
IF (
HASONEVALUE ( Data[Project] ),
SWITCH (
TRUE (),
[Value measure:] = BLANK (), 0,
MAX ( Data[Step] ) = 1, [Value measure:],
MAX ( Data[Step] ) = _minstepaftersteptwo, [Value measure:] - _steponevalue,
[Value measure:]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
Value measure: =
SUM( Data[Value] )
Goal measure: =
VAR _currentproject =
MAX ( Data[Project] )
VAR _minstepaftersteptwo =
MINX (
FILTER (
ALL ( Data ),
Data[Project] = _currentproject
&& Data[Step] > 1
&& Data[Value] <> BLANK ()
),
Data[Step]
)
VAR _steponevalue =
CALCULATE ( [Value measure:], Data[Step] = 1 )
RETURN
IF (
HASONEVALUE ( Data[Project] ),
SWITCH (
TRUE (),
[Value measure:] = BLANK (), 0,
MAX ( Data[Step] ) = 1, [Value measure:],
MAX ( Data[Step] ) = _minstepaftersteptwo, [Value measure:] - _steponevalue,
[Value measure:]
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
68 | |
47 | |
21 | |
19 | |
15 |
User | Count |
---|---|
123 | |
42 | |
39 | |
26 | |
24 |