• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help converting formula for SQRT of MIN to DAX

chakkrav

New Member
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

Well-Known Member
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.
 

vletm

Excel Ninja
chakkrav
As You've read from Forum Rules
  • 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

New Member
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

Well-Known Member
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
Code:
Table.AddColumn(#"Changed Type", "Result", each Number.Sqrt(List.Min({Duration.Days([Date2] - [Date1])/365,1})/1))
 

chakkrav

New Member
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.
 
Top