Hi there,
I have four tables (3 dim, 1 fact table). I have created a measure that reads the two columns from a two-dimension table and based on the value of those columns it calculates the life. The measure looks as follow:
Total Life =
Var _dist = MAX(‘Coastline Distance’[DISTANCE])
Var _500m = MAX(‘Conductor Life Cycle’[Within 500m] )
Var _500mto5k = MAX(‘Conductor Life Cycle’[500m - 5km coast])
Var _5k =MAX(‘Conductor Life Cycle’[> 5km to coast])
Var _conduct_type_size = MAX(‘Conductor Life Cycle’[Conductor Type-Size])
Var _conduct_typ_siz = MAX(‘sde overhead_line_evw’[Conductor Type-Size])
RETURN
IF( _dist < 500, _500m,IF(_dist>=500 && _dist <= 5000 ,_500mto5k,_5k))
When I show the measured value in the table visual, the measure value does not show the correct value. For example, in the following picture:
The value of the total life column of the first row should be 84. But it shows 120. Could anyone help me where I made the mistake?
Sample here
@leo_89,
As written, given the evaluation context you provided in the tables, the measure is returning the correct values. Let’s take a look at the first line:
Based on the filter context of OBJECTID = 1460 in the Coastal Distance table, distance = 94,235.14
This is confirmed in your measure - if you set RETURN _dist at the end, that’s the figure you get.
Now if we look at the final RETURN statement:
= IF( _dist < 500, _500m,IF(_dist>=500 && _dist <= 5000 ,_500mto5k,_5k))
this evaluates out to _5k, given that _dist > 5000.
_5k = MAX(‘Conductor Life Cycle’[> 5km to coast]), which evaluates out to 120.
The problem seems to be that you’ve underspecified your final filter conditions, since there are variables that you calculate in your measure that are never used. In this case, the measure is placing you in the correct column (>5km to coast). Now you just need to add another filter condition that evaluates out to ACSR Less than 100, such that the final answer will be 84.
I hope this is helpful.
1 Like
Hello @leo_89
Please find attached the solution pbix file.
I have used related function to bring > 5km to coast, 500m - 5km coast, Within 500m and DISTANCE columns to sde overhead_line_evw table. Then used calculated column to apply the Conductor Life Cycle conditions that you used.
It turns out that for ObjectId 1460, Conductor Type-Size is “ACSR Greater than 100” where Total Life for distance “> 5km to coast” is 94.
I hope this helps.
Regards
Kumail Razasample_asset_solution.pbix (8.6 MB)
Hi @leo_89, did the response provided by @BrianJ and @Kumail help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
