Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to 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],
", "
)
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] )
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".
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],
", "
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
98 | |
79 | |
64 | |
56 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |