in a table creat a measure base on two column values

[reynold522]

Hi, 

I  would like to creat a  measure base on condition of two table column.

if column: BAT_TYPE is A, and column: CAPACITY_AH is 210, then value should be 168

if column: BAT_TYPE is A, and column: CAPACITY_AH is 100, then value should be 80

if column: BAT_TYPE is E, and column: CAPACITY_AH is 240, then value should be 120

if column: BAT_TYPE is S, and column: CAPACITY_AH is 225, then value should be 45

the dax code writen is as following:

Capacitythreshold = SWITCH(

TRUE(),

Table[BAT_TYPE]="A"&&Table[CAPACITY_AH]=210, 168,

Table[BAT_TYPE]="A"&&Table[CAPACITY_AH]=100, 80,

Table[BAT_TYPE]="E"&&Table[CAPACITY_AH]=240, 120,

Table[BAT_TYPE]="S"&&Table[CAPACITY_AH]=225, 45,

BLANK()

)

I got following error meesage:

A single value for column 'BAT_TYPE' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Thanks in advance!

[FreemanZ · Accepted solution]

hi @reynold522 

A measure depends heavely on the filter context. Without knowing the filter context, there is always issue.

Why don't create a calculated column with the same code and plot a visual with that column?

Actually the way you use SWITCH is the typical way of using SWTCH for calculated columns. 

[reynold522]

thanks, actually I made it before your answer.

Anyway, thanks! I accept your solution as answer. 

[FreemanZ]

hi @reynold522 

It is always the best to raise a question, discuss with someone, then realize the solution by oneself. 

[Samarth_18]

Hi @reynold522 ,

You could use below code if you are using these columns on the visual:-

Capacitythreshold =
SWITCH (
    TRUE (),
    MAX ( Table[BAT_TYPE] ) = "A"
        && MAX ( Table[CAPACITY_AH] ) = 210, 168,
    MAX ( Table[BAT_TYPE] ) = "A"
        && MAX ( Table[CAPACITY_AH] ) = 100, 80,
    MAX ( Table[BAT_TYPE] ) = "E"
        && MAX ( Table[CAPACITY_AH] ) = 240, 120,
    MAX ( Table[BAT_TYPE] ) = "S"
        && MAX ( Table[CAPACITY_AH] ) = 225, 45,
    BLANK ()
)

[reynold522]

sorry it is grammatically correct however, not able to generate the result I need.

Still thanks

[Samarth_18]

Can you share the sample data(raw not screenshot) with expected output pls?