Stuck on DAX formula

[Thstorm]

Hi All,

I'm stuck on a DAX formula. I have the following table:

_Request_ID	LastResponse	_Request_Note_ID	_Request_Note_ID_Hidden	Public_Note_>_7_Days
45342	186	103699	0	0
45342	173	105237	1	0
45342	158	106948	0	0
45342	145	108435	1	0
45342	136	109833	1	0
45342	125	111107	1	0
45342	28	123639	1	0
45342	3	127212	1	0

_Request_ID = is a ticket number (unique per ticket)

LastResponse = Number of days we posted a last response on the ticket

_Request_Note_ID = Unique ID for eacht note on the ticket

_Request_Note_ID_Hidden = 0 is a public note, 1 is a private note

Public_Note_>_7_Days = This column should give a 1 on the last public note (with higest _request_note_ID) when this note is placed more dan 7 days ago. So it should place a one at _request_note_id 106948.

My DAX formula so far:

Public_Note_>_7_Days =
VAR Max_Value =
        CALCULATE (
        MAX ( Query[_Request_Note_ID] ),
        FILTER ( Query, Query[_Request_ID] = EARLIER ( Query[_Request_ID] ) )
    )

RETURN
    IF (AND ( Query[_Request_Note_ID] = Max_Value,Query[LastResponse ] > 7), 1,0 )

Any help is welcome!

Thanks

[Zubair_Muhammad · Accepted solution]

@Thstorm

Seems you missed this parameter only

Public_Note_>_7_Days =
VAR Max_Value =
    CALCULATE (
        MAX ( Query[_Request_Note_ID] ),
        FILTER (
            Query,
            Query[_Request_ID] = EARLIER ( Query[_Request_ID] )
                && [_Request_Note_ID_Hidden] = 0
        )
    )
RETURN
    IF (
        AND ( Query[_Request_Note_ID] = Max_Value, Query[LastResponse ] > 7 ),
        1,
        0
    )

[Thstorm]

Thats it! Thanks!