cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RyanMurtagh Visitor
Visitor

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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

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.

 

 

1 REPLY 1
Highlighted

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

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.