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,
I have two tables, each tble contains country and amount.
How can I use the LookupValue function in Power BI to return the balanc of the country from anoyther table?
Solved! Go to Solution.
Another Way using your tables sample:
ValuesFromTable2 = IF ( HASONEVALUE ( Table1[Country] ), AVERAGEX ( SUMMARIZE ( Table2, Table2[Country], "Values", CALCULATE ( SUM ( Table2[Values] ), FILTER ( Table2, Table2[Country] = VALUES ( Table1[Country] ) ) ) ), [Values] ), CALCULATE ( SUM ( Table2[Values] ) ) )
hi @lcasey
the easiest way in your case is:
1. create a new table with distinct countries
New Table
Countries=Distinct('Table1'[Country])
2. Related with both tables
3. in a table visual put countries table column (Country) and amountsfrom Table 1 and Table 2.
I was hoping to avoid any additional tables as the Countries would then need to be seperatly managed in another table.
How come this DAX formula work on another two tables that are unrelated?
EXCHRATETOUSE =
VAR EXCHDATETOUSE =
MIN ( 'BSA00-MC'[EXCHDATE] )
RETURN
IF (
HASONEVALUE ( 'BSA00-RMMC'[CUSTNMBR] ),
LOOKUPVALUE (
'BSA00-MC'[XCHGRATE],
'BSA00-MC'[CURNCYID], VALUES ( 'BSA00-RMMC'[CCURNCYID] ),
'BSA00-MC'[EXCHDATE], EXCHDATETOUSE
)
I am basically trying to do the same exct thing, but return balances instead of Exchange rates
Another Way using your tables sample:
ValuesFromTable2 = IF ( HASONEVALUE ( Table1[Country] ), AVERAGEX ( SUMMARIZE ( Table2, Table2[Country], "Values", CALCULATE ( SUM ( Table2[Values] ), FILTER ( Table2, Table2[Country] = VALUES ( Table1[Country] ) ) ) ), [Values] ), CALCULATE ( SUM ( Table2[Values] ) ) )
Hmmm---Both Options work, I just cant get numbers to calculate correctly.
Ill need to send this to you offline, This report is a huge pain. I developed it easily in SSRS , but Power BI makes simple things soo complicated. Maybee its the language as SQL is extreemly easy to understand, I find thi language almost impossible to understand.
Thanks!!! and Ill send you details soon on this report. It is a Very complex report and requires many calculations. Hopefully it wont be that bad since you are an expert at DAX.
Hi @lcasey,
Lookupvalue syntax
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)
So you can use the DAX below to create a calculated column.
Column = LOOKUPVALUE(Table1[Amount],Table1[Country],Table2[Country])
Regards,
Charlie Liao
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |