Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Get value from another table using FILTER

Hello all,

 

I'm sure there is an easy way to do this but I can't seem to get the syntax right, please help!

 

I want to get a value from another related table but the search condition is based on whether the string in column "Code" in the current table is found within the "File Name" in the related table.

 

I started with the following:

LOOKUPVALUE('Employee Files'[CompletionDate],
            'Employee Files'[EmployeeFileName], ProceduresByJobRole[FullName],
            FIND( ProceduresByJobRole[Procedures.Course Code], 'Employee Files'[File Name], 1, 0) > 0 )
but it is not recognising the 'Employee Files'[File Name] value in the Find function.
 

I have also tried the following code:

LOOKUPVALUE('Employee Files'[CompletionDate],
    FILTER( 'Employee Files',
            'Employee Files'[EmployeeFileName] = ProceduresByJobRole[FullName]
            && FIND( ProceduresByJobRole[Procedures.Course Code], 'Employee Files'[File Name], 1, 0) > 0 )
but this syntax isn't correct either.
 
Coincidentally, the following does work (for a different column)
COUNTROWS(
    FILTER( 'Employee Files',
            'Employee Files'[EmployeeFileName] = ProceduresByJobRole[FullName]
            && FIND( ProceduresByJobRole[Procedures.Course Code], 'Employee Files'[File Name], 1, 0) > 0 ))
 
Can anyone help please?
Many thanks in advance.
Need Help
  • Wilson_'s avatar
    Wilson_
    2 years ago

    Hi AliPoTD,

     

    I'm guessing this is a calculated column you're adding to the ProceduresByJobRole table, based on the lack of aggregation in your DAX syntax. 😄

    If so, try this out:

    Employee File Completion Date = 
VAR Filtered = 
FILTER (
    'Employee Files',
    FIND ( ProceduresByJobRole[Procedures.Course Code], 'Employee Files'[EmployeeFileName],, -1 ) <> -1
)
VAR Result = 
MINX ( 
    Filtered,
    'Employee Files'[Completion Date]
)

RETURN
Result


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

     

1 Reply

  • Wilson_'s avatar
    Wilson_
    Memorable Member
    2 years ago

    Hi AliPoTD,

     

    I'm guessing this is a calculated column you're adding to the ProceduresByJobRole table, based on the lack of aggregation in your DAX syntax. 😄

    If so, try this out:

    Employee File Completion Date = 
VAR Filtered = 
FILTER (
    'Employee Files',
    FIND ( ProceduresByJobRole[Procedures.Course Code], 'Employee Files'[EmployeeFileName],, -1 ) <> -1
)
VAR Result = 
MINX ( 
    Filtered,
    'Employee Files'[Completion Date]
)

RETURN
Result


    ----------------------------------
    If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)