locked
calculated column for different row context RRS feed

  • 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

    Thanks in advance

    Tuesday, December 12, 2017 11:25 AM

Answers

  • 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.


    http://darren.gosbell.com - please mark correct answers

    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]) 


    http://darren.gosbell.com - please mark correct answers

    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.


    http://darren.gosbell.com - please mark correct answers

    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!

    I am satisfied with your answers. 


    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