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] )
    )
v-huizhn-msft's avatar
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'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] )
    )
    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      8 years ago

      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