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

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
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