Forum Discussion

LuciferMstar's avatar
LuciferMstar
Helper I
2 years ago
Solved

Power Query Next row based on filter

Hey people, I have tried to translate the following Alteryx formula, and I need to apply the same logic in Power BI:  IIF([ID]=[Row+1:ID]&&[STATUS]="On Hold", datetimediff([Row+1:CREATED DATE],[C...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    2 years ago

    =VAR _n=MINX(FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Created Date]>EARLIER(Data[Created Date])),Data[Created Date]) VAR _wo=COUNTROWS(FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Created Date]<EARLIER(Data[Created Date])&&'Data'[Status]="WO")) RETURN IF(_n<>BLANK()&&Data[Status]="On Hold"&&_wo=0,DATEDIFF(Data[Created Date],_n,DAY),0)

    can this work in DAX

LuciferMstar's avatar
LuciferMstar
Helper I
2 years ago

Hi wdx223_Daniel ,

 

The DAX works, thank you!

The M Code just gives an error, something with cyclic.


But I forgot to add that there are some cases where I have "On Hold" after "WO" status by Created Date and in this form it keeps calculating until the end. How do I set a condition that if "On Hold"&&"Created Date" > "WO"&&"Created Date" to do nothing, or ignore it, etc. 

Many thanks!

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
2 years ago
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIyU9JR8i9IzQNSRgaGFroG5roGJkqxOnC5PAWP/JwUJGkjU2RpZK1AZIxHzhKPsfilDQ2AKnCYDJLDr9UQ2cHOOfnFqSiyQINjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, #"Created Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Created Date", type date}, {"ID", Int64.Type}}),
    Custom1 = let a=Table.ToRows(#"Changed Type"),b=List.Skip(a)&{List.Repeat({null},Table.ColumnCount(#"Changed Type"))} in #table(Table.ColumnNames(#"Changed Type")&{"Days"},List.Transform(List.Zip({a,b}),each _{0}&{if _{0}{0}=_{1}{0} and _{0}{1}="On Hold" then Duration.Days(_{1}{2}-_{0}{2}) else 0}))
in
    Custom1

the M code is ok on my PC