cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pmcinnis Frequent Visitor
Frequent Visitor

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

 

Annotation 2020-01-13 155041.jpg

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Re: Need DAX script to create new column from two other columns

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


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

View solution in original post

5 REPLIES 5
Super User II
Super User II

Re: Need DAX script to create new column from two other columns

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


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

View solution in original post

pmcinnis Frequent Visitor
Frequent Visitor

Re: Need DAX script to create new column from two other columns

Thanks for the quick reply, let me look into this

pmcinnis Frequent Visitor
Frequent Visitor

Re: Need DAX script to create new column from two other columns

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

Super User II
Super User II

Re: Need DAX script to create new column from two other columns

@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

 


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

pmcinnis Frequent Visitor
Frequent Visitor

Re: Need DAX script to create new column from two other columns

Ah, I see, thanks for clarifying!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors