locked
Lookup inside calculated column RRS feed

  • Question

  • Hi

    My simplistic data model looks like this: 

    Table.1
    Spread Product Sales Channel ..
    2 x CB ..
    2 y CB
    4 x GFR ..
    4 y GFR ..

    I need a column with Spread Mapping, which should look like this:

    ->There are more columns in data model. 

    -> Spread for GFR is wrong so it must be mapped to be able to compare with CB

    I guess there should be calculated column formula to calculate Spread Mapping.

    Any idea how to do it?


    Tuesday, October 13, 2015 4:22 PM

Answers

  • I'd say do this, then:

    Spread Mapping=
    LOOKUPVALUE(
        Table1[Spread]
        ,Table1[Product]
        ,Table1[Product]
        ,Table1[SalesChannel]
        ,"CB"
    )
    But this does not work with the extended sample you provided because in that sample Table1[Product] and Table1[SalesChannel] are not a unique key, but map to multiple distinct values of [Spread].

    If your extended sample is representative, then you cannot do a straight lookup and you'll need something like this:

    Spread Mapping=
    CALCULATE(
        MIN(Table1[Spread])
        ,ALLEXCEPT(Table1, Table1[Product])
        ,Table1[SalesChannel] = "CB"
    )

    GNet Group BI Consultant

    • Proposed as answer by Michael Amadi Friday, October 16, 2015 11:44 PM
    • Marked as answer by Bartek Wachocki Monday, October 19, 2015 8:54 AM
    Thursday, October 15, 2015 1:48 PM

All replies

  • Sorry, but it seems completely unclear what you need to look up here. What formulas have you tried so far, what results do you get, and how are we able to look up the appropriate value?

    In your example you have indicated that [Spread Mapping] should be 2 for every row in your table, but why? Are we looking up the minimum value based on the value of [Product]?

    GNet Group BI Consultant

    Tuesday, October 13, 2015 9:32 PM
  • Sure, I will explain

    Let's extend this table and add more rows. So we have the following

    I have tried this: (not working as I expected)


    =CALCULATE(sum([Spread]);FILTER(ALL('Table.1'[Spread]);Table.1[Sales Channel]="CB"))

    Spread Mapping (for CB) = Spread (for CB)

    but for GFR we are looking for adequate values in CB (means: all conditions the same, except Sales Channel)

    Spread Mapping (for GFR) = Spread (for CB) 



    Tuesday, October 13, 2015 10:25 PM
  • Ok Let's simplify my question

    Is it possible to use calculated column formula to match values from another column in the same table in data model?


    Wednesday, October 14, 2015 10:45 PM
  • The answer is yes, but that's not a satisfactory answer for you because it's not actionable.

    If it's exact matching of a number of attributes, then LOOKUPVALUE() is likely best

    If it's something that needs an inequality, then some form of CALCULATE() with some combination of MIN(), MAX(), VALUES(), ALLEXCEPT(), ALL(), and FILTER() will be required, and the specifics depend on the exact need.

    Your SUM() will fail (not be a lookup, but an aggregation) when there's more than one entry per unique combination of lookup criteria.

    Which attributes have to be an exact match?

    Which attributes have an inequality test for the lookup?

    Which attributes should be ignored in the lookup?

    How many rows are there for the potential lookup?

    How many attributes out of the total are used in the lookup, how many ignored?


    GNet Group BI Consultant

    Thursday, October 15, 2015 12:30 AM
  • Thanks

    I know my SUM is not perfect and may return not the expected values in some cases. 

    That's why I wrote this thread.

    Which attributes have to be an exact match?

    Product & Spread Mapping (for CB) must be matched with Product & Spread Mapping (for GFR)

    Which attributes have an inequality test for the lookup?

    Product & Spread Mapping & Slaes Channel is a unique combination

    Which attributes should be ignored in the lookup?

    I am not sure what it means but I guess it should be Slaes Channel

    How many rows are there for the potential lookup?

    100.000 more or less

    How many attributes out of the total are used in the lookup, how many ignored?

    Let's focus only on the atributes on the picture above. I need a logic. I will adjust formula to my needs.


    Thursday, October 15, 2015 8:28 AM
  • I'd say do this, then:

    Spread Mapping=
    LOOKUPVALUE(
        Table1[Spread]
        ,Table1[Product]
        ,Table1[Product]
        ,Table1[SalesChannel]
        ,"CB"
    )
    But this does not work with the extended sample you provided because in that sample Table1[Product] and Table1[SalesChannel] are not a unique key, but map to multiple distinct values of [Spread].

    If your extended sample is representative, then you cannot do a straight lookup and you'll need something like this:

    Spread Mapping=
    CALCULATE(
        MIN(Table1[Spread])
        ,ALLEXCEPT(Table1, Table1[Product])
        ,Table1[SalesChannel] = "CB"
    )

    GNet Group BI Consultant

    • Proposed as answer by Michael Amadi Friday, October 16, 2015 11:44 PM
    • Marked as answer by Bartek Wachocki Monday, October 19, 2015 8:54 AM
    Thursday, October 15, 2015 1:48 PM