Forum Discussion

kkalyanrr's avatar
kkalyanrr
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's avatar
    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] )
    )
kkalyanrr's avatar
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's avatar
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


  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    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] )
    )