Need DAX script to create new column from two other columns

[pmcinnis]

In the table below, the column 'final result' is the new column that I need to produce from columns 'unit' and 'result'. The rules for creating 'final result' are:

• For unit x, final result = result x 1,000
• For unit y, final result = result
• For unit z, final result = result/1,000
• For null unit and null result,  final result = null
• For null unit and numerical result, final result = result

Question: What is the DAX script to produce the column 'final result'?

Thanks in advance

[az38 · Accepted solution]

Hi @pmcinnis 

try a new calculated column

final result = SWITCH(TRUE(),
'Table'[unit]="x",1000*'Table'[result],
'Table'[unit]="y",'Table'[result],
'Table'[unit]="z",'Table'[result]/1000,
ISBLANK('Table'[unit]) && ISBLANK('Table'[result]),BLANK(),
ISBLANK('Table'[unit]) && ISNUMBER('Table'[result]),'Table'[result],
"Undefined"
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[pmcinnis]

Thanks, that worked great. What is the purpose of the TRUE function near the beginning of the script?

[az38]

@pmcinnis 

SWITCH syntax is

SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, <else>)  

in the most common case as expression used Column.

it works as follow:

First, value1 compares with expression. if  equals = result`, if not - goes to value2 and so on.

As you have more sophisticated condition in values, you can not to compare it with the only column. So, TRUE() allows you to  compare complex conditions with true(), like if 

ISBLANK('Table'[unit]) && ISBLANK('Table'[result]) = TRUE()

then return desired result

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[pmcinnis]

Ah, I see, thanks for clarifying!

[pmcinnis]

Thanks for the quick reply, let me look into this