Concetenate for each if value is bigger then

[HBL_Nick]

Hi Guys,

I want to make a calculated column of who should have approved an invoice, so that I can compare it with who actually approved it.

The tables look like this:

Workflow:

Invoices:

When the Invoice Amount is >= than workflow minimal amount. All the employees uptil that point have to approve it. The output should be like this:

So that eventually I could compare who should've approved and who did approve and get the final table:

[selimovd]

Hey @HBL_Nick ,

the following calculated column should give you the right Approver:

Responsible Approver =
VAR vWorkflowID = Invoices[Workflow]
VAR vAmount = Invoices[Amount]
VAR vTable =
    CALCULATETABLE (
        Workflow,
        Workflow[WorkflowID] = vWorkflowID,
        Workflow[MinimalAmount] <= vAmount
    )
VAR vFilteredTable =
    FILTER (
        vTable,
        Workflow[MinimalAmount] = MAXX ( vTable, Workflow[MinimalAmount] )
    )
RETURN
    MAXX ( vFilteredTable, Workflow[Approver] )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[HBL_Nick]

Hi @selimovd, thanks for helping me out 🙂 This is almost the answer to my question, the only thing is: with this answer, I will find the last approver of the workflow / invoice, but I actually would like a list of approvers of the invoice / workflow. For example: an invoice with workflow 1 and a amount of 800, 2 people Should have approved. So the calculated column should contain: "Peter, James". Now it will only contain: "James". 

[selimovd · Accepted solution]

Hey @HBL_Nick ,

I thought you want the first one that would be allowed 😉

Then try the following measure:

Responsible Approver =
VAR vWorkflowID = Invoices[Workflow]
VAR vAmount = Invoices[Amount]
VAR vTable =
    CALCULATETABLE(
        Workflow,
        Workflow[WorkflowID] = vWorkflowID,
        Workflow[MinimalAmount] <= vAmount
    )
RETURN
    CONCATENATEX(
        vTable,
        Workflow[Approver],
        ", "
    )

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[HBL_Nick]

Hi @selimovd , this is exactly what I needed, thanks for the help!