Assign 1 to earliest Finish date based on conditions

[jbeans]

Hi All 

Any help with the below would be much appreciated 

I have order table where an order Id can appear multiple times. I want to look at the earliest modified date and check if the rejection column is blank. If so assign a value of 1. If the user deselects that rejection reason, and the next earlies is row is blank I would like a value of 1 to still be assigned. However if there is a reason, 0 should be assigned. 

See my code below - 1 is getting applied to each row incorrectly

Thanks in advance 

Rejection Count = 
VAR FirstOrderDate = MINX(FILTER('lnd AWComponentLibraryApproval', 
    'lnd AWComponentLibraryApproval'[Approver_Role] = "LOC REVIEWER / APPROVER"), 'lnd AWComponentLibraryApproval'[Approval_finish_date])

RETURN
CALCULATE(
    1,
    FILTER(
        'lnd AWComponentLibraryApproval',
        'lnd AWComponentLibraryApproval'[Approval_finish_date] = FirstOrderDate &&
        ISBLANK('lnd AWComponentLibraryApproval'[Reason_for_rejection]) &&
        'lnd AWComponentLibraryApproval'[Approver_Role] = "LOC REVIEWER / APPROVER"
    )
)

[Anonymous · Accepted solution]

Hi @jbeans,

You can try to use the following measure formula if it suitable for you requirements:

Rejection Count =
VAR FirstOrderDate =
    MINX (
        FILTER ( 'Table1', 'Table1'[Approver_Role] = "LOC REVIEWER / APPROVER" ),
        'Table1'[Approval_finish_date]
    )
RETURN
    IF (
        'Table1'[Approval_finish_date] = FirstOrderDate
            && 'Table1'[Approver_Role] = "LOC REVIEWER / APPROVER",
        IF (
            'Table1'[Reason_for_rejection] = BLANK ()
                || 'Table1'[Reason_for_rejection] = "",
            1,
            0
        )
    )

Regards,

Xiaoxin Sheng

[jbeans]

Hi @Anonymous , thanks for your reply. It looks like that might work, however the intellisense will not let me select my finish date column on line 10, only measures. Am I missing something?

[Anonymous]

HI @jbeans,

The 'finish' should be replaced to the variable that you define in above, the underline highlighted part should be changed to the table and field names that you mentioned to check with conditions.

Regards,

Xiaoxin Sheng