Search code examples
powerbidaxetlbusiness-intelligencedaxstudio

DAX Alternate function for TOP SQL statement

We use this SQL statement for getting top data value in a column

SELECT TOP 1 NOTE_DETAIL 
FROM CLAIM_NOTES 
WHERE CLAIM_NO = C.CLAIM_NO AND ISNULL(DELETED, 0) = 0 
ORDER BY CREATED_DATE DESC

How we can we use this approach in a DAX query if we want last_note from note detail?

Solution

  • For your data:

    enter image description here

    The DAX measure:

    Last Note Detail =
VAR MaxCreatedDate =
    CALCULATE (
        MAX ( CLAIM_NOTES[CREATED_DATE] ),
        ALLEXCEPT ( CLAIM_NOTES, CLAIM_NOTES[CLAIM_NO] )
    )
VAR Result =
    CALCULATE (
        MAX ( CLAIM_NOTES[NOTE_DETAIL] ),
        CLAIM_NOTES[CREATED_DATE] = MaxCreatedDate
    )
RETURN
    Result

    Gives a visual of:

    enter image description here