Forum Discussion

rifdhyh's avatar
rifdhyh
Regular Visitor
4 years ago
Solved

Getting Values by MAXIMUM Alphabatic Letter for Distinct Records

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

 

Alphabatic
MAXX
Measures to Columns
  • tamerj1's avatar
    tamerj1
    4 years ago

    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
    )

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

     

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
    rifdhyh
    Regular Visitor
    4 years ago

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

  • rifdhyh's avatar
    rifdhyh
    Regular Visitor
    4 years ago

    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