# calculated column for different row context

• ### Question

• Here is my sample data set (table1):

 year category segment group concept_id version sales 2016 FR male EDT 11111 DA 10 2016 FR male EDT 11112 DA 11 2016 FR male EDC 11113 DA 12 2016 FR female EDP 11114 DA 13 2016 FR female EDP 11115 DA 14 2016 FR male EDT 11111 OE 11 2016 FR male EDT 11112 OE 9 2016 FR male EDC 11113 OE 12 2016 FR female EDP 11114 OE 12 2016 FR female EDP 11115 OE 13 2017 FR male EDT 11111 DA 11 2017 FR male EDT 11112 DA 12 2017 FR male EDC 11113 DA 13 2017 FR female EDP 11114 DA 14 2017 FR female EDP 11115 DA 15 2017 FR male EDT 11111 OE 13 2017 FR male EDT 11112 OE 11 2017 FR male EDC 11113 OE 14 2017 FR female EDP 11115 OE 14

I am trying to get the calc column formula for "sales_2" to work like this

The screen shows the logic for 11111 concept_id.

sales_2 means - return the sales for OE version instead of DA

I guess there is probably not the one solution

Tuesday, December 12, 2017 11:25 AM

• Hi Bartek,

Thanks for your question.

You can also try to use DAX LOOKUPVALUE Function to create a calculated column as blow:

```sales_2 =
IF (
Table1[version] = "DA",
LOOKUPVALUE (
Table1[sales],
Table1[Year], Table1[Year],
Table1[Category], Table1[Category],
Table1[Segment], Table1[Segment],
Table1[group], Table1[group],
Table1[concept_id], Table1[concept_id],
Table1[version], "OE"
),
LOOKUPVALUE (
Table1[sales],
Table1[Year], Table1[Year],
Table1[Category], Table1[Category],
Table1[Segment], Table1[Segment],
Table1[group], Table1[group],
Table1[concept_id], Table1[concept_id],
Table1[version], "DA"
)
)```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, December 13, 2017 1:36 AM
• I ask cos in real scenario sometimes you want more columns to add and do not willing to change calc columns formulas?

It's also possible that additional columns should not be included by default. For example, if you added a column for a tax amount you most likely would not want it to be included in the look up as it's not part of the composite key. Having said that you could do something like the following:

=maxx(
CALCULATETABLE(Table1,
filter(
all(Table1[version],Table1[sales])
,Table1[version] <> EARLIER(table1[version])
)
)
,Table1[sales])

Note that you have to include any columns you don't want as part of the lookup inside the ALL() function that is being filtered.

I actually hit this issue when trying to double checked this calculation against my previous calculated column expression. As when I added a second calculated column it was including this second calculated column in the look up so I had to manually add it into the ALL() so that I could compare the two calculations.

Wednesday, December 13, 2017 9:53 PM

### All replies

• One possible solution is an expression like the following:

=maxx(filter(Table1,
Table1[year]=EARLIER(Table1[year]) &&
Table1[category] = EARLIER(Table1[category]) &&
Table1[segment]=EARLIER(Table1[segment]) &&
Table1[group]=EARLIER(Table1[group]) &&
Table1[concept_id]=EARLIER(Table1[concept_id]) &&
Table1[version] <> EARLIER(Table1[version])),[sales])

Tuesday, December 12, 2017 7:43 PM
• Hi Bartek,

Thanks for your question.

You can also try to use DAX LOOKUPVALUE Function to create a calculated column as blow:

```sales_2 =
IF (
Table1[version] = "DA",
LOOKUPVALUE (
Table1[sales],
Table1[Year], Table1[Year],
Table1[Category], Table1[Category],
Table1[Segment], Table1[Segment],
Table1[group], Table1[group],
Table1[concept_id], Table1[concept_id],
Table1[version], "OE"
),
LOOKUPVALUE (
Table1[sales],
Table1[Year], Table1[Year],
Table1[Category], Table1[Category],
Table1[Segment], Table1[Segment],
Table1[group], Table1[group],
Table1[concept_id], Table1[concept_id],
Table1[version], "DA"
)
)```

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Wednesday, December 13, 2017 1:36 AM
• Thanks Darren, Thanks Willson,

Both your solutions work great!
One more question. Is it to be done without reflecting to columns?:

Table1[Year]
Table1[Category]
Table1[Segment]
Table1[group]
Table1[concept_id]

I ask cos in real scenario sometimes you want more columns to add and do not willing to change calc columns formulas?

Wednesday, December 13, 2017 11:14 AM
• I ask cos in real scenario sometimes you want more columns to add and do not willing to change calc columns formulas?

It's also possible that additional columns should not be included by default. For example, if you added a column for a tax amount you most likely would not want it to be included in the look up as it's not part of the composite key. Having said that you could do something like the following:

=maxx(
CALCULATETABLE(Table1,
filter(
all(Table1[version],Table1[sales])
,Table1[version] <> EARLIER(table1[version])
)
)
,Table1[sales])

Note that you have to include any columns you don't want as part of the lookup inside the ALL() function that is being filtered.

I actually hit this issue when trying to double checked this calculation against my previous calculated column expression. As when I added a second calculated column it was including this second calculated column in the look up so I had to manually add it into the ALL() so that I could compare the two calculations.

Wednesday, December 13, 2017 9:53 PM
• Hi Bartek,

Thanks for your response.

I am glad to know that both solutions works great for you. Please kindly mark them as answers. By doing so, it will benefit all community members who are facing similar issues. Your contribution is highly appreciated.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Thursday, December 14, 2017 12:53 AM
• Thanks both of you guys. It all works!

Thursday, December 14, 2017 3:25 PM
• Although the second Darren's answer (which I marked as correct one) may by tricky in some scenarios (when new calc column added) I decided to use the first one.

inspired by you I come up also on the following one which may be usefull  too:

```=
CALCULATE (
MAX ( Table1[sales] ),
ALLEXCEPT (
Table1,
Table1[year],
Table1[category],
Table1[segment],
Table1[group],
Table1[concept_id]
),
Table1[version] <> EARLIER ( Table1[version] )
)
```
cheers

Thursday, December 14, 2017 3:53 PM
• Hi Bartek,

Thanks for your response.

Please kindly mark all the solutions as answers, thanks for your understanding and support.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Friday, December 15, 2017 1:06 AM