Forum Discussion

Thstorm's avatar
Thstorm
Frequent Visitor
7 years ago
Solved

Stuck on DAX formula

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
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    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
    )

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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
    )