Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Help me to write the DAX formula using Filter Function to Link Tables

I have the four following table: Table 1: (Main Table) Date ID Full Time Equivalent 30/06/2023 100 30.0 30/06/2023 200 20.0 31/05/2023 100 30.0 31/05/2023 200 20.0   T...
  • ERD's avatar
    ERD
    3 years ago

    Anonymous ,

    FTE =
CALCULATE (
    SUM ( 'Table 1'[Full Time Equivalent] ),
    FILTER ( 'Table 4', 'Table 4'[Date] = SELECTEDVALUE ( 'Table 2'[Date Key] ) )
)

     

  • ERD's avatar
    ERD
    3 years ago

    Anonymous , are you using Excel or PBI? Try this:

    FTE =
VAR dt = MAX (  'Table 2'[Date Key] )
RETURN
CALCULATE (
    SUM ( 'Table 1'[Full Time Equivalent] ),
    FILTER ( 'Table 4', 'Table 4'[Date] = dt )
)
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thanks ERD. Unfortunately it shows the following message: Failed to resolve name 'SELECTEDVALUE'. It is not a valid table, variable, or function name.

ERD's avatar
ERD
Icon for Community Champion rankCommunity Champion
3 years ago

Anonymous , are you using Excel or PBI? Try this:

FTE =
VAR dt = MAX (  'Table 2'[Date Key] )
RETURN
CALCULATE (
    SUM ( 'Table 1'[Full Time Equivalent] ),
    FILTER ( 'Table 4', 'Table 4'[Date] = dt )
)