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,
I've got two tables, one is my transaction in MYOB and the other freight pick up details.
The problem I've got is that, my freight sometimes has more than on order in the consignment and I need to work out what has left but not been invoiced yet.
So what I am trying to do is look up in my MYOB query, if the "SO Number" field from the MYOB query is contained in the "Sender References" field of the freight table, to pull the "Actual Pickup Date" column into my MYOB query as a new column.
I have tried contain, search, find, lookupvalue functions and haven't found a solution yet.
Can anyone help me find a solution to this one please?
MYOB Table (called "MYOB - Open Orders" in my query):
Freight Table (called "General Freight - Daily Freight" in my query):
Solved! Go to Solution.
Hi, @Kmcdonald ;
You could create a column as follows:
Column =
var _1=[SO Number]
return
MAXX(
CALCULATETABLE(
VALUES('Freight'[Actual Pickup Date]),
CONTAINSSTRING('Freight'[Sender References],_1))
,[Actual Pickup Date])
The final output is shown below:
Or create a measure as follows:
Measure =
MAXX(
CALCULATETABLE(
VALUES('Freight'[Actual Pickup Date]),
CONTAINSSTRING('Freight'[Sender References],MAX('MYOB'[SO Number]))),[Actual Pickup Date])
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks guys 🙂
Appreciate your help and got the result I needed off your suggestions.
Hi, @Kmcdonald ;
You could create a column as follows:
Column =
var _1=[SO Number]
return
MAXX(
CALCULATETABLE(
VALUES('Freight'[Actual Pickup Date]),
CONTAINSSTRING('Freight'[Sender References],_1))
,[Actual Pickup Date])
The final output is shown below:
Or create a measure as follows:
Measure =
MAXX(
CALCULATETABLE(
VALUES('Freight'[Actual Pickup Date]),
CONTAINSSTRING('Freight'[Sender References],MAX('MYOB'[SO Number]))),[Actual Pickup Date])
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Kmcdonald , You can create a new column like in the transaction
maxx(filter(MYOB , MYOB [sender reference] = transaction[sender reference] && MYOB [SO Number] = transaction[SO Number] ) , MYOB[Actual Pickup Date])
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 |
---|---|
47 | |
27 | |
23 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |