Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Code this DAX formula in M

Hello,

I would like to code this DAX formula in Power Query M ? What is the solution please ?

Lat =
var Notif='TASK'[Notification]
var Task_Number='TASK'[Task number]
var Task_Number_Suivant=CALCULATE(MIN('TASK'[Task number]), FILTER('TASK', Notif='TASK'[Notification] && Task_Number<'TASK'[Task number]))
var lat=CALCULATE(MAX('TASK'[Latitude]), FILTER('TASK', Notif='TASK'[Notification] && Task_Number_Suivant='TASK'[Task number]))
return lat
 
Thank you !
Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    Please create a custom column.

    = 
let
a = Table.SelectRows(PreviousStepName,(x)=>x[Notification]=[Notification] and x[Task number]>[Task number]),
b = List.Min(a[Task number]),
c = List.Max(Table.SelectRows(a,(y)=>y[Task number]=b)[Latitude])
in 
c

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you very much for your help, it works but it takes a lot of time to load, to you have another solution for large dataset please ?

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    let

    Source=TASK,

    Custom1=let a=Table.Group(Source,"Notification",{"n",each Table.Sort(_,"Task number"){0}[Latitude]}) in Table.AddColumn(Source,"lat",each a{[Notification=[Notification]]}[n])

    in

    Custom1

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello, thank you for your help. It says "Expression.Error: A cyclic reference was encountered during evaluation."

      • wdx223_Daniel's avatar
        wdx223_Daniel
        Community Champion
        3 years ago

        let

        Source=TASK,

        Custom1=Table.FromRecords(List.Accumulate(Table.ToRecords(Table.Sort(Source,{"Notification","Task number"})),{{},"",0},(x,y)=>if x{1}=y[Notification] then {x{0}&{y&[lat=x{2}]]},x{1},x{2}} else {x{0}&{y&[lat=y[Latitude]]},y[Notification],y[Latitude]}){0})

        in

        Custom1