March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I want to create a simple measure but I fail to get it.
There are actual and plan values. I want to create a measure that sums actual values as soon as there are any values. If there is no actual value what so over the measure should use the plan value.
I have tried to write a measure but it is not working.
New Measure =
var actual_values_maxDate =
CALCULATE(
MAX( 'Table'[Date] ),
ALL( 'Table' ),
'Table'[Planning Type] = "actual"
)
RETURN
IF(
//Logical Test
SELECTEDVALUE( 'Date'[Date] ) > actual_values_maxDate,
//if true
CALCULATE(
SUM( 'Table'[Value] ),
FILTER( 'Table', 'Table'[Planning Type] = "plan" )
),
//if false
CALCULATE(
SUM( 'Table'[Value] ),
FILTER( 'Table', 'Table'[Planning Type] = "actual" )
)
)
Solved! Go to Solution.
Based on your example, you can try this measure,
measure =
var _actual= MIN('Table'[Actual])
var _plan= MIN('Table'[Plan])
return IF(ISBLANK(_actual),_plan,_actual)
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Based on your example, you can try this measure,
measure =
var _actual= MIN('Table'[Actual])
var _plan= MIN('Table'[Plan])
return IF(ISBLANK(_actual),_plan,_actual)
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
@v-xiaotang Thank you. That seemed to work.
I have adjusted the formular a little bit according to my case
FTE =
value instead.
var _actual= CALCULATE( SUM('Internal'[FTE] ),'Internal'[Planning Type] = "actual" )
var _plan= CALCULATE( SUM('Internal'[FTE] ),'Internal'[Planning Type] = "plan" )
RETURN
IF(
//logical test
ISBLANK(_actual),
//if true
_plan,
//if false
_actual
)
@raymondpocher , Try with small change
New Measure =
var actual_values_maxDate =
CALCULATE(
MAX( 'Table'[Date] ),
Filter( ALL( 'Table' ),
'Table'[Planning Type] = "actual" && not(isblank('Table'[Value] )) )
)
RETURN
Sumx( values(Table[Year]), IF(
//Logical Test
Max( 'Date'[Date] ) > actual_values_maxDate,
//if true
CALCULATE(
SUM( 'Table'[Value] ),
FILTER( 'Table', 'Table'[Planning Type] = "plan" )
),
//if false
CALCULATE(
SUM( 'Table'[Value] ),
FILTER( 'Table', 'Table'[Planning Type] = "actual" )
)
) )
@amitchandak I dont know why but for some reason I wasnt able to manage it with that formular.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
86 | |
84 | |
76 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |