Looking up values

[kkalyanrr]

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

[Zubair_Muhammad · Accepted solution]

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

[v-huizhn-msft]

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]

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

Hi @kkalyanrr,

As I tested, you can still create a calculated column in Power BI desktop using RANDBETWEEN function to identify each row, and use the DAX to get expected result. The DAX formula still can be used in Power BI desktop.

Best Regards,
Angelia

[v-huizhn-msft]

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

[Zubair_Muhammad · Accepted solution]

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 · Accepted solution]

@kkalyanrr