Forum Discussion

lcm7721's avatar
lcm7721
Frequent Visitor
6 months ago
Solved

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 foun...
Need Help
  • lcm7721's avatar
    lcm7721
    6 months ago

    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.

Gabry's avatar
Gabry
Icon for Super User rankSuper User
6 months ago

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

  • lcm7721's avatar
    lcm7721
    Frequent Visitor
    6 months ago

    thank you all for your help, I tried all suggested measures, and this is the error I get: 

     

    • Gabry's avatar
      Gabry
      Icon for Super User rankSuper User
      6 months ago

      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
    )
)