Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a dataset that contains unique units (as in products) that were sold. In the dataset, there is a column for a "scan date", as in, when it was scanned to originally be delivered to us before we sold it. I have a unique key in this table that groups the "category" and "facility source" as one key, so something like "electronicsnewyork" as an example.
In another table, I have a key that can match the original key exactly, because it is a list of categories and facility sources, plus a column that is for the "iteration" of the product, with a min and a max date field that tell me the dates of which the iteration is true. See an example below.
key iteration min date max date
electronicsnewyork 1 1/1/2019 3/30/2019
electronicsnewyork 2 3/31/2019 8/30/2019
electronicsnewyork 3 9/1/2019
What I'm trying to do is look up key against key, and then look at the "scan date" and see which min-max date range it falls under in the other table, and then tell me what iteration the product is based on that.
I have 144 combinations of keys in total, so an efficient solution would be amazing!
Solved! Go to Solution.
Hi @dojobrady ,
I created a measure that may help you.
Measure =
CALCULATE (
MAX( Table1[Iteration] ),
FILTER (
Table1,
AND (
MAX ( Table2[Scan date] ) >= Table1[Min date],
MAX ( Table2[Scan date] ) <= Table1[Max date]
)
|| AND (
MAX ( Table2[Scan date] ) >= Table1[Min date],
Table1[Max date] = BLANK ()
)
)
)
I attached my sample that you can download. If this is not what you want, please share more details and post the expected result. Then we will understand clearly about your request.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.
Hi @dojobrady ,
I created a measure that may help you.
Measure =
CALCULATE (
MAX( Table1[Iteration] ),
FILTER (
Table1,
AND (
MAX ( Table2[Scan date] ) >= Table1[Min date],
MAX ( Table2[Scan date] ) <= Table1[Max date]
)
|| AND (
MAX ( Table2[Scan date] ) >= Table1[Min date],
Table1[Max date] = BLANK ()
)
)
)
I attached my sample that you can download. If this is not what you want, please share more details and post the expected result. Then we will understand clearly about your request.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.