Forum Discussion

pmcinnis's avatar
pmcinnis
Icon for Helper III rankHelper III
6 years ago
Solved

Need DAX script to create new column from two other columns

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...
Need Help
  • az38's avatar
    az38
    6 years ago

    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's avatar
pmcinnis
Icon for Helper III rankHelper III
6 years ago

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

az38's avatar
az38
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
    pmcinnis
    Icon for Helper III rankHelper III
    6 years ago

    Ah, I see, thanks for clarifying!