topic Getting Values by MAXIMUM Alphabatic Letter for Distinct Records in DAX Commands and Tips

Getting Values by MAXIMUM Alphabatic Letter for Distinct Records

rifdhyh — Thu, 30 Jun 2022 05:23:50 GMT

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

Re: Getting Values by MAXIMUM Alphabatic Letter for Distinct Records

tamerj1 — Thu, 30 Jun 2022 06:34:12 GMT

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

Re: Getting Values by MAXIMUM Alphabatic Letter for Distinct Records

rifdhyh — Thu, 30 Jun 2022 07:56:32 GMT

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

Re: Getting Values by MAXIMUM Alphabatic Letter for Distinct Records

tamerj1 — Thu, 30 Jun 2022 08:31:20 GMT

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 )

Re: Getting Values by MAXIMUM Alphabatic Letter for Distinct Records

rifdhyh — Fri, 01 Jul 2022 11:58:19 GMT

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