Getting Values by MAXIMUM Alphabatic Letter for Distinct Records

[rifdhyh]

Hi All,
Good day..!
May I ask how can I get the values for "For Resubmit" Column by below conditions

My Data Table as below

1. Consider only Latest Revision (Lastest Alphbatical Letter) for each RFA No.
2. If Engineer Response of those records are "Data Incomplete" or "Rejected" then need to count as 1 in "For Resubmit" Column

As per above table I can get values for these records only>

Noted:
I have used below DAX Function to get Maximum Revision number
= UNICHAR(MAXX(DISTINCT('RFA'[Rfa No]),MAX(UNICODE('RFA'[Revision])))
I need to find how can I apply Specific Values in "Engineer response" column to find "Resubmit" Values

Thanks in Advance

Rifdhy

[rifdhyh]

Dear @tamerj1 ,
This DAX Worked properly..
Thanks a lot for you kind response
May Allah bless you...!

[tamerj1 · Accepted solution]

Hi @rifdhyh 
Her eis the sample file with the solution https://we.tl/t-83VSAA2vug

For Resubmit = 
VAR CurrentRevision =
    RFA[Revision]
VAR CurrentResponse =
    RFA[Engineers Response]
VAR CurrentRFATable = 
    CALCULATETABLE ( RFA, ALLEXCEPT ( RFA, RFA[RFA No] ) )
VAR LatestRevision =
    MAXX ( CurrentRFATable, RFA[Revision] )
RETURN
    IF ( 
        CurrentRevision = LatestRevision 
            && CurrentResponse IN { "Data Incomplete, please resubmit", "Rejected" },
        1
    )

[rifdhyh]

Hi dear ,
Thanks for your comment,
Simply I need to get
Sum for  "For Resubmit" column,
by fiter latest revision for each RFAs if only Engineer Response is "Data Incomplete" or "Reject"

In above table ony RFA No. "

BIA-RFA-AW-TSC-MT-21-0271

gives latest revision (A) as requested , so only for this RFA should count 1

I need the DAX code for this, Hope you undestood

Thanks a lot for your reply,

Rifdhy

[tamerj1]

@rifdhyh 

I don't fully understand the rest of the conditions (I believe you can handle this part) however, for the last revision you can use

Maximum Revision number =
MAXX (
    CALCULATETABLE ( 'RFA', ALLEXCEPT ( 'RFA', 'RFA'[Rfa No] ) ),
    'RFA'[Revision]
)