Need support with Calculated measures

[MaQaN]

Hello,

I have a problem that I need help with as I’m unable to find a solution myself.

I currently have a table visualization set up as bellow where I’m summarizing data from multiple query’s.

What I want is to have a Calculated measure that will show the Stock zone for Location "ZZ" / "OO" / "YY" / "XX" for each distinct order line number on each Delivery number.

Transaction Date	Order number	Delivery number	Order line number	Stock Zone	Location	Stock Zone_2 (TryingToAdd)
2021-03-19	3000000015	1000000023	5	E	EE	Z
2021-03-18	3000000015	1000000023	5	D	DD	Z
2021-03-17	3000000015	1000000023	5	Z	ZZ	Z
2021-03-16	3000000015	1000000023	5	B	BB	Z
2021-03-15	3000000015	1000000023	5	A	AA	Z
2021-03-11	3000000015	1000000023	7	E	EE	O
2021-03-10	3000000015	1000000023	7	D	DD	O
2021-03-09	3000000015	1000000023	7	O	OO	O
2021-03-08	3000000015	1000000023	7	B	BB	O
2021-03-07	3000000015	1000000023	7	A	AA	O
2021-02-24	3000000006	1000000019	1	E	EE	Y
2021-02-23	3000000006	1000000019	1	D	DD	Y
2021-02-22	3000000006	1000000019	1	Y	YY	Y
2021-02-21	3000000006	1000000019	1	B	BB	Y
2021-02-20	3000000006	1000000019	1	A	AA	Y
2021-01-09	3000000001	1000000003	2	E	EE	X
2021-01-08	3000000001	1000000003	2	D	DD	X
2021-01-07	3000000001	1000000003	2	X	XX	X
2021-01-06	3000000001	1000000003	2	B	BB	X
2021-01-05	3000000001	1000000003	2	A	AA	X

I will later on filter the table to only have distinct rows for delivery number/Order line number where I will keep the oldest date ones. 

Transaction Date	Order number	Delivery number	Order line number	Stock Zone	Location	Stock Zone_2 (TryingToAdd)
2021-03-19	3000000015	1000000023	5	E	EE	Z
2021-03-11	3000000015	1000000023	7	E	EE	O
2021-02-24	3000000006	1000000019	1	E	EE	Y
2021-01-09	3000000001	1000000003	2	E	EE	X

Is this something that's solvable using Calculated measures?

I’m unable to use calculated columns unfortunate.

Hope i have been cleared enough with what im trying to have solved here.

Thanks in advance,

Marcus L

[v-deddai1-msft · Accepted solution]

Hi @MaQaN ,

Use the measure below:

Measure = CALCULATE(MAX('Table'[Stock Zone]),FILTER(ALL('Table'),'Table'[Order line number] = MAX('Table'[Order line number])&&'Table'[Location] IN{ "ZZ", "OO", "YY" ,"XX"}))

For more details, please refer to the pbix file.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[m3tr01d]

Hi @MaQaN 

can you clarify for Line number 5 why is the last column is showing Z?
What is the rule to compute the last column?

[MaQaN]

Hello @m3tr01d,

A new order line number is generated for each distinkt part on the same Delivery number. So the delivery number 1000000023 from the table consist of atleast 5 difrent parts.

Example:

Deliver number 1000000023

Line 1 = Phone

Line 2 = Computer

Line 3 = Headset

...

All these parts will then go on and be delivered to customer that placed the Order number for specified delivery number.

"What is the rule to compute the last column?"

As for the last column showing Z is what im trying to accomplise. It's should show me the specifik Stock Zone that im intrested in on each order line and where it previus have been to.

In my mind im thinking something like:

IF Stock Zone is "AA" then "Z" en return "Z" as long as Delivery number & Order line number is the same.