; WITH CTE AS (
SELECT Id, "DT-time", Other_ID,
nextOther_ID = LEAD(Other_ID) OVER(ORDER BY Id)
FROM tbl
)SELECT Id, "DT-time", Other_ID
FROM CTE
WHERE nextOther_ID IS NULL
How to get the non null row before the row with null value
Learner DAX
41
Reputation points
Hi
I want to see the non null row before the row with NULL value in the result set.
I have data in below in format
I want to see ID 2 row in the result set like below
Help with this would be appreciated.
2 answers
Sort by: Most helpful
-
Erland Sommarskog 112.7K Reputation points MVP
2024-10-14T20:32:29.5066667+00:00 -
LiHongMSFT-4306 28,041 Reputation points
2024-10-15T01:29:10.57+00:00 Hi @Learner DAX
Based on Erland's code, how about modifying where clause like this:
; WITH CTE AS ( SELECT Id, "DT-time", Other_ID, nextOther_ID = LEAD(Other_ID) OVER(ORDER BY Id) FROM tbl )SELECT Id, "DT-time", Other_ID FROM CTE WHERE nextOther_ID IS NULL AND Other_ID IS NOT NULL
Best regards,
Cosmog
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".