Count of Companies that have been rejected and not resubmitted

[BotBot1]

Hi all,

I am trying to get a measure that will be a distinct count of company ids where their latest action contains the string "reject", is greater than 2 weeks old, and excludes companies that have had newer actions that include either strings "accept" or "Submitted". Please see below for examples:

company_id 1335 would be in the count, as the have rejections, not had a newer "accept"/"Submitted" action, and is older than 2 weeks ago.

company_id 1085 would not be included, as they had a rejection but have had subsequent submittals/acceptances.

Here is my attempted DAX:

Rejections Sent Not Submitted (2W) 2 = 

VAR TwoWeeksAgo = TODAY() - 14

VAR LatestReject = CALCULATE(MAX(vw_FT_Company_Registration_BI[RegistrationLog_DateCreated]), 
                    CONTAINSSTRING(vw_FT_Company_Registration_BI[RegistrationLog_Action], "reject"))

VAR LatestAcceptOrSubmit = CALCULATE(MAX(vw_FT_Company_Registration_BI[RegistrationLog_DateCreated]), 
                            CONTAINSSTRING(vw_FT_Company_Registration_BI[RegistrationLog_Action], "accept") ||
                            CONTAINSSTRING(vw_FT_Company_Registration_BI[RegistrationLog_Action], "Submitted"))

RETURN

CALCULATE(DISTINCTCOUNT(vw_FT_Company_Registration_BI[company_id]), 
            vw_FT_Company_Registration_BI[RegistrationLog_DateCreated] <= TwoWeeksAgo,
                LatestReject > LatestAcceptOrSubmit)

Please let me know if any further explanation is needed, thank you!

[Anonymous · Accepted solution]

Hi @BotBot1 ,

Please try this measure:

Rejections Sent Not Submitted (2W) 2 = 
VAR TwoWeeksAgo = TODAY() - 14
VAR RejectRows =
    FILTER (
        'vw_FT_Company_Registration_BI',
        SEARCH("reject", 'vw_FT_Company_Registration_BI'[RegistrationLog_Action], 1, 0) > 0
            && 'vw_FT_Company_Registration_BI'[RegistrationLog_DateCreated] < TwoWeeksAgo
    )
VAR AcceptSubmitRows =
    FILTER (
        'vw_FT_Company_Registration_BI',
        (
            SEARCH("accept", 'vw_FT_Company_Registration_BI'[RegistrationLog_Action], 1, 0) > 0
                || SEARCH("Submitted", 'vw_FT_Company_Registration_BI'[RegistrationLog_Action], 1, 0) > 0
        )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'vw_FT_Company_Registration_BI'[company_id] ),
        FILTER (
            RejectRows,
            NOT (
                CALCULATE (
                    COUNTROWS ( 'vw_FT_Company_Registration_BI' ),
                    FILTER (
                        AcceptSubmitRows,
                        'vw_FT_Company_Registration_BI'[company_id] = EARLIER ( 'vw_FT_Company_Registration_BI'[company_id] )
                            && 'vw_FT_Company_Registration_BI'[RegistrationLog_DateCreated] > EARLIER ( 'vw_FT_Company_Registration_BI'[RegistrationLog_DateCreated] )
                    )
                ) > 0
            )
        )
    )

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[BotBot1]

Amazing, thank you so much!