Verifying a two step invoice approval process

[Daniel_L]

Hello community,

How would you solve this case where we need to verify whether a two step invoice approval process is legitimate or not?

I have two tables containing information about given approvals in phase A and phase B, and want to see if the invoices have been approved in accordance with the regulation.

Rules:

• If there is one A approver and one B approver, they cannot be the same person
• If there is one A approver then A is allowed to be one of many B approvers
• If there are more than one A approver then none of them can be a B approver
• If there are more than one A approver and more than one B approver, then at least one B approver must not be a A approver.

An alternate way of declaring the rules: Remove all B approvers from the A approvers set; if there are any remaining approvers then the process is legitimate.

Sample tables:

Phase A

InvoiceID_A	UserID_A
A	John
A	Sarah
A	Brad
B	Brad
C	Brad
D	Jane
D	Sarah
E	Jane

Phase B

InvoiceID_B	UserID_B
A	Sarah
B	John
B	Sarah
C	Sarah
C	Brad
D	John
D	Sarah
D	Brad
D	Jane
E	Jane

Expected Result

InvoiceID	(Result_UserIDs)	Result
A	(null)	False
B	John, Sarah	True
C	Sarah	True
D	John, Brad	True
E	(null)	False

Best Regards,

Daniel

[sturlaws · Accepted solution]

Hi @Daniel_L 

Will this strategy work for you?

First create a new table with the distinct InvoiceIDs like this

InvoiceID =
DISTINCT (
    UNION ( VALUES ( 'Phase A'[InvoiceID_A] ); VALUES ( 'Phase B'[InvoiceID_B] ) )
)

Then create a new calculated column using this code

Number of =
VAR phaseA_approvers =
    CALCULATETABLE (
        VALUES ( 'Phase A'[UserID_A] );
        FILTER ( VALUES ( 'Phase A' ); 'Phase A'[InvoiceID_A] = InvoiceID[InvoiceID] )
    )
VAR phaseB_approvers =
    CALCULATETABLE (
        VALUES ( 'Phase B'[UserID_B] );
        FILTER ( VALUES ( 'Phase B' ); 'Phase B'[InvoiceID_B] = InvoiceID[InvoiceID] )
    )
VAR phaseB_approvers_not_approvers_phaseA =
    FILTER ( phaseB_approvers; NOT ( [UserID_B] IN phaseA_approvers ) )
RETURN
    IF (
        COUNTROWS ( phaseB_approvers_not_approvers_phaseA ) > 0;
        TRUE ();
        FALSE ()
    )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.