Reply
Highlighted
Frequent Visitor
Posts: 4
Registered: ‎06-28-2018
Accepted Solution

Stuck on DAX formula

[ Edited ]

Hi All,

 

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

 

_Request_IDLastResponse _Request_Note_ID_Request_Note_ID_HiddenPublic_Note_>_7_Days
4534218610369900
4534217310523710
4534215810694800
4534214510843510
4534213610983310
4534212511110710
453422812363910
45342312721210

 

_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

Accepted Solutions
Super User
Posts: 2,829
Registered: ‎09-27-2017

Re: Stuck on DAX formula

@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
    )

View solution in original post


All Replies
Super User
Posts: 2,829
Registered: ‎09-27-2017

Re: Stuck on DAX formula

@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
    )
Frequent Visitor
Posts: 4
Registered: ‎06-28-2018

Re: Stuck on DAX formula

Thats it! Thanks!