Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] ) )
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
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..
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
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
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] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |