Help converting formula for SQRT of MIN to DAX

[chakkrav]

Excel Formula is as below :

MATURITYFACTOR = =SQRT(MIN(([@[MATURITY_DATE]]-[@RUNDATE])/365,1)/1)

I tried implementing the below dax in Custom Column as below. But its throwing error. Any advise!

= Table.AddColumn(#"Reordered Columns", "MATURITYFACTOR", each Number.Sqrt(List.Min(([MATURITY_DATE]-[RUNDATE])/365,1)/1))

 

[GraH - Guido]

That's DAX? Looks suspiciously more like M to be honest.
You did not feed a list object tot the list function: syntax is something like List.Min ({ListOfValues}).
Before nesting functions, it does not hurt to create intermediate columns. Once each piece is working you can simply delete the columns and eventually even delete the steps and combine all into one step. However the number of steps is not always an issue for the performance. And also, when nesting functions, often PQ will work from the inside out and create "virtually" the same helper columns. Many times the impact on performance is even negative.

 

[GraH - Guido]

crossposted here : Help converting formula for SQRT of MIN to DAX | MrExcel Message Board

 

[vletm]

chakkrav
As You've read from Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[chakkrav]

That's DAX? Looks suspiciously more like M to be honest.
You did not feed a list object tot the list function: syntax is something like List.Min ({ListOfValues}).
Before nesting functions, it does not hurt to create intermediate columns. Once each piece is working you can simply delete the columns and eventually even delete the steps and combine all into one step. However the number of steps is not always an issue for the performance. And also, when nesting functions, often PQ will work from the inside out and create "virtually" the same helper columns. Many times the impact on performance is even negative.

Apologies for cross posting , yes its M. I understand its List.Min ({ListOfValues}).
I did try creating helper columns as below. But still i got stuck with M for creating Col B

Col A - (MATURITY_DATE-RUNDATE)/365
Col B - Number.Sqrt(List.Min((Col A ,1)/1))

Still getting error. Hence seeking out help.

 

[GraH - Guido]

I understand its List.Min ({ListOfValues}).

Seems you don't understand fully the correct syntax, as It should be surrounded by curly brackets and you forgot them. Try

Table.AddColumn(#"Changed Type", "Result", each Number.Sqrt(List.Min({Duration.Days([Date2] - [Date1])/365,1})/1))

 

[chakkrav]

Thanks. It worked.
Could you also advise on the statistical function NORM.S.DIST([value],TRUE) to be used in power query.
Unable to use this function & also here https://docs.microsoft.com/en-us/dax/norm-s-dist-dax it states that
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Not sure how to break up the function / any other method to make it work with helper columns in power query.
Appreciate an advise in this regards.