Forum Discussion

Daniel_L's avatar
Daniel_L
Frequent Visitor
6 years ago
Solved

Verifying a two step invoice approval process

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

Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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.

1 Reply

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    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.