Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

DAX Help

Hi there.

See sample of a dataset I am working on below.

M1Reg	M1End	NumericalChangePretoPost
1	3	2
4	3	-1
2		-2

I am trying to calculate the change between M1Reg column and M1End column. However some of the M1End responses I have are blank and I don't want the DAX to calculate the change like it has done on the 3rd row.

Currently using

NumericalChangePreToPost = M1[ M1End]-M1[ M1Reg]

What can I insert to the DAX to ensure that it doesnt return an answer if the M1End column is blank?

Anonymous
4 years ago
NumericalChangePreToPost =if(not(isblank(M1[ M1End])),
M1[ M1End]-M1[ M1Reg])

7 Replies

Anonymous
Not applicable
4 years ago
Hello
NumericalChangePreToPost =if(isblank(M1[ M1End]),-M1[ M1Reg],
M1[ M1End]-M1[ M1Reg])
- Anonymous
  Not applicable
  4 years ago
  Hi there.
  
  Thank you for your response..
  
  It is still giving me back a calcuation even if the cell was blank though?
  - y3wan
    Frequent Visitor
    4 years ago
    Couldn't you creat a new column of;
    newcolumn = IF(M1END=0, Blank(), M1[M1END]-M1[M1REG])
    
    For blank values in either 1 column
    newcolumn = IF(M1REG=0, Blank(), IF(M1END=0, Blank(), M1[M1END]-M1[M1REG]))
    
    fyi I meant Blank ( ) bucket in the formulas above
Anonymous
Not applicable
4 years ago
With the if statement it should return negative value of first column
- Anonymous
  Not applicable
  4 years ago
  I don't want it to return a negative value of the first column, I want it not to calculate anything if either the first or second column is blank. It is currently assuming a blank cell = 0 which isnt the case.
  
  What can I insert to ensure it doesn't calculate on a blank cell?
  - Anonymous
    Not applicable
    4 years ago
    NumericalChangePreToPost =if(not(isblank(M1[ M1End])),
    M1[ M1End]-M1[ M1Reg])

Related content