locked
lookup in the same table RRS feed

  • Question

  • Hi All

    I have a data model looking like this:

    Table 1.

    Flyer code Article Link
    1 Fujifilm   Finepix Jv 500 14.0 Mp link 1
    1 Fujifilm Finepix Sl 300 14.0 Mp link 2
    1 Lg 32 Ln5400   Led "32 link 3
    2 Lg 42 La640s Led "42 link 4
    2 Lg 42 Ln5400   Led "42 link 5
    2 Lg 47 La640s Led "47 link 6

    (there are more columns but it is not necessary to show all)

    What I want to do is to add calculated column with lookuplinks. I do not need link to a given article but one link for each flyer which could be represented by any of article's links. So my data model should look like this:

    Table 1.

    Flyer code Article Link Lookuplink
    1 Fujifilm   Finepix Jv 500 14.0 Mp link 1 link 1
    1 Fujifilm Finepix Sl 300 14.0 Mp link 2 link 1
    1 Lg 32 Ln5400   Led "32 link 3 link 1
    2 Lg 42 La640s Led "42 link 4 link 4
    2 Lg 42 Ln5400   Led "42 link 5 link 4
    2 Lg 47 La640s Led "47 link 6 link 4

    How to do this?


    Wednesday, December 17, 2014 12:22 PM

Answers

  • =CALCULATETABLE(
        TOPN( 1
            , VALUES( 'table'[Link] )
            , 'table'[Link]
            , 1
        )
        , ALLEXCEPT( 'table', 'table'[Flyer code] )
    )
    • Proposed as answer by Michael Amadi Wednesday, December 17, 2014 8:28 PM
    • Marked as answer by Bartek Wachocki Thursday, December 18, 2014 12:27 PM
    Wednesday, December 17, 2014 3:47 PM

All replies

  • Hi Gordonik,

    Can you please explain in more detail.

    On which condition you want which link to be shown as Lookuplink


    • Edited by ameyjoe Wednesday, December 17, 2014 1:28 PM
    Wednesday, December 17, 2014 1:27 PM
  • As you can see in my source Table 1 I have a Flyer code "1" corresponding with three links (Link 1, Link, 2 , Link 3)

    Each link connect to given article and indirectly also to  a Flyer with code 1 .

    In my pivot table based on this model I want to show the Flyer code corresponding with just one link. It could be

     (Link 1, Link, 2 , Link 3) doesn't matter which one because all of them are assign  not only to article but also to Flyer with code 1.

    This is how my pivot should look like

    Pivot. 1

    Flyer code     Link

    code 1          Link 1

    code 2          Link 4

     

    This is how my pivot looks like now:

    Pivot 2.

    Flyer code     Link

    code 1          Link 1

                        Link 2

                        Link 3

    code 2          Link 4

                        Link 5

                        Link 6

    I do not need (Link 2, Link 3, Link 5, Link 6) in my pivot table.

    Wednesday, December 17, 2014 2:43 PM
  • =CALCULATETABLE(
        TOPN( 1
            , VALUES( 'table'[Link] )
            , 'table'[Link]
            , 1
        )
        , ALLEXCEPT( 'table', 'table'[Flyer code] )
    )
    • Proposed as answer by Michael Amadi Wednesday, December 17, 2014 8:28 PM
    • Marked as answer by Bartek Wachocki Thursday, December 18, 2014 12:27 PM
    Wednesday, December 17, 2014 3:47 PM
  • Great!

    Thanks Greg


    Thursday, December 18, 2014 12:27 PM