topic Re: in a table creat a measure base on two column values in DAX Commands and Tips

in a table creat a measure base on two column values

reynold522 — Tue, 10 Jan 2023 11:00:35 GMT

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!

Re: in a table creat a measure base on two column values

Samarth_18 — Tue, 10 Jan 2023 11:03:54 GMT

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 () )

Re: in a table creat a measure base on two column values

reynold522 — Tue, 10 Jan 2023 12:30:10 GMT

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

Still thanks

Re: in a table creat a measure base on two column values

Samarth_18 — Tue, 10 Jan 2023 14:47:10 GMT

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

Re: in a table creat a measure base on two column values

FreemanZ — Tue, 10 Jan 2023 16:03:59 GMT

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.

Re: in a table creat a measure base on two column values

reynold522 — Tue, 10 Jan 2023 16:06:53 GMT

thanks, actually I made it before your answer.

Anyway, thanks! I accept your solution as answer.

Re: in a table creat a measure base on two column values

FreemanZ — Tue, 10 Jan 2023 16:10:34 GMT

hi reynold522

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