Extract data per ID form different table

[ZanneMari]

Hi Everyone, I have been struggling with a visualisation. I have two tables:

1. Open PO table. This table tells me if the Purchase Order is still Open and what is the value of the PO per line. 

2. History PO - This table has many lines and a lot of duplicate values. It tells me the followingL

 a) Of the Open PO line item does not exist in this table there is no history yet  (I need a colum that returs "has history")

 b) Pf all the transaction lines for each PO Line it tells me what type of transaction has happened - State is E then D then Q. I need to determine the latest state and then extract the sum of Value (without duplicates) e.g. if the PO ID has 10 transactions E's and D's, the current state is D becuase D>E. Final state is Q but if there is no Q's then its still on D state

c) I need to determine the Sum of the latest state but there are duplicates. 

Desired Outcome: O would like to add a colum in the Open PO table for a,b,c above, obviously for those that has no history b and c will be N/A or blank. I can then also calculate spend left for thos that have a history. Net Value - SUM(Value) = Spend left

[v-hashadapu · Accepted solution]

Hi @ZanneMari , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @ZanneMari , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @ZanneMari , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.

[bhanu_gautam]

@ZanneMari 

You can create a calculated column in the Open PO table to check if the PO line item exists in the History PO table.

HasHistory = IF(
COUNTROWS(
FILTER(
HistoryPO,
HistoryPO[PO Number] = OpenPO[PO Number] &&
HistoryPO[Item ID] = OpenPO[Item ID]
)
) > 0,
TRUE,
FALSE
)

Create a calculated column to determine the latest state for each PO line item.

LatestState =
VAR CurrentStates =
FILTER(
HistoryPO,
HistoryPO[PO Number] = OpenPO[PO Number] &&
HistoryPO[Item ID] = OpenPO[Item ID]
)
VAR MaxState =
MAXX(CurrentStates, SWITCH(HistoryPO[State], "E", 1, "D", 2, "Q", 3))
RETURN
SWITCH(MaxState, 1, "E", 2, "D", 3, "Q", BLANK())

Create a measure to sum the values for the latest state without duplicates.

dax
SumLatestStateValue =
VAR LatestState = [LatestState]
VAR UniqueValues =
SUMX(
FILTER(
DISTINCT(
SELECTCOLUMNS(
HistoryPO,
"PO Number", HistoryPO[PO Number],
"Item ID", HistoryPO[Item ID],
"State", HistoryPO[State],
"Value", HistoryPO[Value]
)
),
[PO Number] = OpenPO[PO Number] &&
[Item ID] = OpenPO[Item ID] &&
[State] = LatestState
),
[Value]
)
RETURN
IF([HasHistory], UniqueValues, BLANK())

Finally, create a calculated column to calculate the spend left.

SpendLeft = OpenPO[Net Value] - [SumLatestStateValue]

[ZanneMari]

Thank you so much for your help Bhanu.