Looking up values

Hello Team,

I'm using lookupvalue function to retrieve a value from a different table, and my requirement is little different.

Please help me with this.

Source TableMain TableExpected Result

Hi kkalyanrr

Try this solution

In your main Table, add a calculated column

Randnumber =
RANDBETWEEN ( 1, 10000 )

Now you can do that LookUp using this Calculated Column

Sales =
VAR MaxNumber =
    CALCULATE (
        MAX ( MainTable[Randnumber] ),
        ALLEXCEPT ( MainTable, MainTable[ID] )
    )
RETURN
    IF (
        MainTable[Randnumber] = MaxNumber,
        LOOKUPVALUE ( SourceTable[Sales], SourceTable[Id], MainTable[ID] )
    )

Zubair_Muhammad
8 years ago
kkalyanrr

6 Replies

v-huizhn-msft
Microsoft Employee
8 years ago
Hi kkalyanrr,

I reproduce your scanrio and get expected result, please follow steps below.

1. Add a index column in Main table by clicking add index column from 1 in Query Editor->Apply&close. Create a relationship between Source and Main tables.

2. Create a calculated column using the formula.

Column = IF ( Main[Index] = CALCULATE ( MIN ( Main[Index] ), ALLEXCEPT ( Main, Main[Id] ) ), LOOKUPVALUE ( Source[Sales], Source[Id], Main[Id] ), BLANK () )

3. Create a new table only including [Id] and [Sales] columns. Click New Table under Modeling on Home page, type the formula and get expected result.

Best Regards,
Angelia
- kkalyanrr
  Helper V
  8 years ago
  v-huizhn-msft
  
  Thanks for the reply.
  
  My source table is an Union table , so I cannot add an Index Column. Is there any other way to achieve this result.
  
  Thanks,
  Kalyan..
  - v-huizhn-msft
    Microsoft Employee
    8 years ago
    Hi kkalyanrr,
    
    Got it, you can not edit the table because your resouce table is created by DAX. But you have to add index column to identity each row, if you can add index column in the original table used to create your Union table?
    
    Best Regards,
    Angelia