Converting a basic Excel formula into DAX for a new column in Power BI

[RyanMurtagh]

Hi,

So basically I made the below formula in Excel (I know it is a mess)

=IF(TODAY()-D9<30,1,IF(AND(TODAY()-D9>=30,TODAY()-D9<90),2,IF(AND(TODAY()-D9>=90,TODAY()-D9<180),3,IF(AND(TODAY()-D9>=180,TODAY()-D9<270),4,IF(AND(TODAY()-D9>=270,TODAY()-D9<360),5,IF(TODAY()-D9>=360,6))))))

D9 is a standin for where column I will need to use. Essentially I need this column to display a specific value based on the range it is in (using todays date - D9)

Any ideas?

[SteveCampbell · Accepted solution]

Hello,

DAX resembles many excels formulas. As a side note, if you have 2016 excel that is updated, you can use the IFS function that eliminates the need for nested if statements. 

This function is not in Power BI, so you can use nested IF. Personally, I would use the SWITCH:

You can do this in a measure:

Measure = 
var _val = TODAY() - SELECTEDVALUE(Table1[Column1])
RETURN
SWITCH(
 TRUE(),
 _val < 30 , 1,
 _val < 90 , 2,
 _val < 180 , 3,
 _val < 270 , 4,
 _val < 360 , 5,
 6
)

and change Table1[Column1] to the value from D9.

If you do need a column, simply remove "SELECTEDVALUE". However, it owuld be more eficient as a measure, or to write in Power Query.

The section:

var _val = TODAY() - SELECTEDVALUE(Table1[Column1])

will store as a variable so you don't need to write (and calculate) it every time.

SWITCH will match the first expression with a value. The first expression is TRUE(), so it will go through each value ( _val <30, _Val < 60 etc) and match the first one that is TRUE, and return the result.