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.
Hi All
I'm trying to create a calulated column that uses the project ID in Table 1 and looks it up in Table 2 however it needs to match not just the 'Project ID' but also the term "Yes" in the 'Latest Reporting Entity' column, to return the value in the 'Reporting Entity' column.
Please see the example below. On Table 1 I have entered the Reporting Entity I would expect it to be.
Does anyone have any suggestions on how I could solve this? I tried using LOOKUPVALUE function, however as there are multiple lines for some projects I think that's causing it not to work.
Thanks very much in advance.
Chris
Solved! Go to Solution.
Hi @birdie29,
You’d better create a middle table which is used to filter the Table2 where the Latest Reporting is "Yes". Then use the LOOKUPVALUE function. Please try the following solution.
First, create a middle table using CALCULATETABLE function. Please review the formula and screenshot.
Table 3 = CALCULATETABLE(Table2,FILTER(Table2,Table2[Latest Reporting]="Yes"))
Then create a calculated column using LOOKUPVALUE function, and get desired result.
Reporting Entity = LOOKUPVALUE('Table 3'[Reporting Entity],'Table 3'[Project ID],Table1[Project ID])
Best Regards,
Angelia
Hi @birdie29,
You’d better create a middle table which is used to filter the Table2 where the Latest Reporting is "Yes". Then use the LOOKUPVALUE function. Please try the following solution.
First, create a middle table using CALCULATETABLE function. Please review the formula and screenshot.
Table 3 = CALCULATETABLE(Table2,FILTER(Table2,Table2[Latest Reporting]="Yes"))
Then create a calculated column using LOOKUPVALUE function, and get desired result.
Reporting Entity = LOOKUPVALUE('Table 3'[Reporting Entity],'Table 3'[Project ID],Table1[Project ID])
Best Regards,
Angelia
Thanks for your quick response.
Just to confirm, am I creating a calulated column within a new table or the exisiting table? Either way when I have tried to create this I get the following error message:
Thank you
Chris
Hi @birdie29,
I am very gald you have understood creatting a table. If you have any question, please feel free to ask. If you have resolved your issue, please mark the corresponding reply or share your solution for help more people. Thanks for understanding.
Best Regards,
Angelia
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |