Aggregation from different tables and create a dimension

[CK_WAKE]

Hi there, I have created the measure below and it works fine; from that, I need to create dimensions. The columns come from different tables. I appreciate your help on this.

Below formula giving me the correct values.
Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI

However, when I use the above formula to create dimension as show below, it does not show up, even though it does not give me any error.

if(Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)>0 and
Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)<60, 'Good',
if(Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)>61 and
Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)<150, 'Fair',
if(Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)>151 and
Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)<240, 'Bad',
if(Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)>241 and
Max(WO_COUNT_KPI) + Max(K1VA_AGE_KPI)+ Max(NOMINAL_VOLTAGE_KPI)+Max(EQUIPMENT_AGE_KPI)<300, 'Very Bad'))))

[marcus_sommer · Accepted solution]

It should return an error because you are using aggregation-functions without applying an aggr() wrapping to specify the dimensional context for the calculations. Further possible errors may be a timeout or a ran out of memory message - because this kind of calculation could become very heavy especially if the parts come from different tables and/or aren't associated suitable.

Beside this you are using +- operators between the operands. This will only be working if all parts return always a valid number - if not the entire calculation will become NULL. This could be fetched with a range-function and in the end your expression may go in this direction:

aggr(if(rangesum(max(A), max(B), max(C), max(D)) < 60, 'x',
        if(rangesum(max(A), max(B), max(C), max(D)) < 150, 'y',
        if(....))),
Dim1, Dim2)

[CK_WAKE]

Thanks for the help