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

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!