Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pmcinnis
Helper III
Helper III

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
az38
Community Champion
Community Champion

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
az38
Community Champion
Community Champion

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

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

az38
Community Champion
Community Champion

@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

Ah, I see, thanks for clarifying!

Thanks for the quick reply, let me look into this

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.