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

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

5 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity 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
      Icon for Helper III rankHelper III
      6 years ago

      Thanks for the quick reply, let me look into this

    • 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