Forum Discussion

Helper V

8 years ago

Solved

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.

Need Help

Tips and Tricks

Zubair_Muhammad

8 years ago

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

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