a measure or column calculated base on multiple condition

[reynold522]

Hi all, 

I have a table like following

Battery Type	Setup	Capacity	Threshold Capacity
1	1	100	80%*Capacity
2	1	70	30%*Capacity
3	1	100	10%*capacity
1	2	70	200%*capacity
2	2	100	120%*capacity

the column 1-3 are already exist, the forth column is the one that I would like to implement

the value calculated in forth coloumn will be depended on column 1-3, if condition.

what will be DAX code for this case?

I would like to use threshold to set alert, is column or measure better for this case?

Thanks in advance

[FreemanZ]

hi @reynold522 

the pattern is not that obvious. could you explain the logic behind?

[reynold522]

Yeah, the threshold (unit capacity) is heavily depend on battery nominal capacity and type of battery as well as battery setup.  

e.g., 1st row, if battery type is 1, setup is 1 and capacity is 100, then threshold according to our experimental results should set as 80%*capacity, in this row, capacity is 100, then the value should be 80

[v-rzhou-msft · Accepted solution]

Hi @reynold522 ,

I suggest you to create a 'Threshold' table and then calculate based on it.

Create a calculated column in your capacity table.

Threshold Capacity = 
VAR _Threshold =
    CALCULATE (
        SUM ( Threshold[Threshold] ),
        FILTER (
            Threshold,
            Threshold[Battery Type ] = EARLIER ( 'Table'[Battery Type ] )
                && Threshold[Setup] = EARLIER ( 'Table'[Setup] )
        )
    )
RETURN
    'Table'[Capacity] * _Threshold

 Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.