Forum Discussion

pmcinnis's avatar
pmcinnis
Helper 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
Community Champion
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
    Helper III
    6 years ago

    Thanks for the quick reply, let me look into this

  • pmcinnis's avatar
    pmcinnis
    Helper 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
      Community 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
        Helper III
        6 years ago

        Ah, I see, thanks for clarifying!