Forum Discussion
Return "Specific value" from table 1, if not found, return value from table 2
Hello,
I have Table A and Table B, and I need to create a visual to show the Project Name when selecting a Project Number.
Is it possible to create a calculated formula to return Proj Name if found in Table A, if not return Proj Name from Table B.
Thank you,
LC
Hi, I found a solution by apending the two tables, concatenating both Proj Name columns and filtering out the blank and duplicates.
Thank you all for your replies.
13 Replies
hey lcm7721 ,
sure it's possible, you can do that like this
Project Name = COALESCE( LOOKUPVALUE('TableA'[Proj Name], 'TableA'[Proj Number], 'TableB'[Proj Number]), LOOKUPVALUE('TableB'[Proj Name], 'TableB'[Proj Number], 'TableB'[Proj Number]) )Or another possible approach
Project Name = VAR NameA = LOOKUPVALUE('TableA'[Proj Name], 'TableA'[Proj Number], 'TableB'[Proj Number]) RETURN IF( NOT ISBLANK(NameA), NameA, LOOKUPVALUE('TableB'[Proj Name], 'TableB'[Proj Number], 'TableB'[Proj Number]) )If it's a measure and you need to catch the prject number selected you can use SELECTEDVALUE
thank you all for your help, I tried all suggested measures, and this is the error I get:
Is that a measure or a calculated column?
Try like this:
Measure = VAR _ProjNum = SELECTEDVALUE ( TableA[Project Number] ) RETURN COALESCE( LOOKUPVALUE( TableA[Proj Name], TableA[Proj Number], _ProjNum ), LOOKUPVALUE( TableB[Proj Name], TableB[Proj Number], _ProjNum ) )
Hello,
I would need to know a bit more about how the Project Number connects to each table. One way to consider is to create a crosswalk between your two tables containing a list of unique project numbers, and connect Table A and B to this cross walk.
From there, you can then just pull in the Project number into the slicer to filter Tables A and B respecitvely - no calculated formulas needed.
Again, without knowing much of your data, this answer may change if the two project tables are not mutually exclusive.There are multiple tables, all joint by the project number. However, not all have the project name. Only two tables have this information. I could merge them and create a unique proj name column(table) but I thought a calculated formula was doable.
Please try the measure below:
Project Name = VAR _ProjNum = SELECTEDVALUE ( TableA[Project Number] ) VAR _NameFromA = CALCULATE ( MAX ( TableA[Proj Name] ), TableA[Project Number] = _ProjNum ) VAR _NameFromB = CALCULATE ( MAX ( TableB[Proj Name] ), TableB[Project Number] = _ProjNum ) RETURN COALESCE ( _NameFromA, _NameFromB )Measure...Let me try as a Calculated column 🙂
Whilst Gabry 's solution may get your answer. Please keep in mind the documented issues with using LOOKUPVALUE;
"However, in certain conditions it could include CallbackDataID requests in storage engine queries, resulting in slower execution, lower performance and missed cache hits. When this happens, alternative approaches should be considered"Project Name =
VAR ProjNum = YourTable[Project Number]
VAR NameA = LOOKUPVALUE(TableA[Proj Name], TableA[Project Number], ProjNum)
RETURN
IF(
ISBLANK(NameA),
LOOKUPVALUE(TableB[Proj Name], TableB[Project Number], ProjNum),
NameA
)Thankyou, Gabry, ExcelMonke, cengizhanarslan ,Kedar_Pande and danextian for your responses.
Hi lcm7721,
Based on our understanding of the scenario, please find attached the screenshots and a sample PBIX file that may assist in resolving the issue.
We hope the information provided will help resolve the issue. Should you have any further queries, kindly feel free to contact the Microsoft Fabric Community.
Thank you.Hi, I found a solution by apending the two tables, concatenating both Proj Name columns and filtering out the blank and duplicates.
Thank you all for your replies.
Hi lcm7721,
We are pleased to note that your issue has been resolved. Thank you for sharing your insights and approach in resolving the issue, which will be beneficial to other members of the community. Should you have any further queries, please feel free to contact the Microsoft Fabric community.
Thank you.
