Forum Discussion

LuciferMstar's avatar
LuciferMstar
Icon for Helper I rankHelper 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],[CREATED DATE],"days"), 0)

 

I need your help as I can't understand why it doesn't work.  I'm not sure if it works in DAX, but from what I read it doesn't. 

 

Basically I need to get the number of days for each ID between a date and status and the next date after that status. 

I've tried using the Index methods and other Solved cases, but I can't seem to make it to work. 

In the following table I'm getting the result as Days, the difference in days between the On Hold Status and its Created Date and the next Status that is not On Hold and its created date. 

 

IDStatusCreated DateDays
226Open04/07/20180
226On Hold25/07/20189
226Open03/08/20180
226Open09/08/20180
226On Hold09/08/201861
226On Hold08/10/20181
226Open09/10/20180
226On Hold15/10/20183
226Closed18/10/20180

 

Many thanks!

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

7 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    2 years ago

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

    M Code:

    NewStep= let a=Table.ToRows(YourTableName),b=List.Skip(a)&{List.Repeat({null},Table.ColumnCount(YourTableName))} in #table(Table.ColumnNames(YourTableName)&{"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}))


     

    • LuciferMstar's avatar
      LuciferMstar
      Icon for Helper I rankHelper 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
        Icon for Community Champion rankCommunity 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