Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
92 | |
87 | |
77 | |
73 | |
68 |
User | Count |
---|---|
116 | |
107 | |
88 | |
65 | |
63 |