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)

DateIDFull Time Equivalent
30/06/202310030.0
30/06/202320020.0
31/05/202310030.0
31/05/202320020.0

 

Table 2: (Primary Key)

Date Key
30/06/2023
31/05/2023
30/04/2023
31/03/2023

 

Table 3: (Primary Key)

ID Key
100
200
300

 

Table 4: (ID Table)

DateIDTeamDepartmentDivision
30/06/2023100XYZMNOOPQ
31/05/2023100ZXYMNOOPQ
30/04/2023100ZXYLOMOPQ

 

Here are the relationship details:

1) Table 1 [Date] > Many to One>Table 2 [Date Key]>One to Many> Table 4 [Date]

2) Table 1 [ID] > Many to One>Table 3 [ID Key]>One to Many> Table 4 [ID]

 

As the ID moves to various teams and departments every month, I need to create a measure that will give the summation of full-time equivalents by date, department, division, etc. as follows:

 

FTE = CALCULATE(

SUM(Table1[Full Time Equivalent]),

FILTER(Table 4, Table 4[Date] = 'Table 2'[Date Key])

)

 

This measure will first filter Table 4 in the Date column. Then, it will calculate the sum of the full-time equivalent column in Table 1 for the filtered rows. It is showing an error as follows: "This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."


Can anyone please help me write the formula so that FTE will filter by date, department, division, etc.?

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

6 Replies

  • JoeBarry's avatar
    JoeBarry
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi Anonymous 

     

    Please try

     

    FTE = CALCULATE(

SUM(Table1[Full Time Equivalent]),

FILTER(Table 4, Table 4[Date] = MAX('Table 2'[Date Key]))

)

     

    Thanks

    Joe

     

    If this post helps, then please Accept it as the solution

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks JoeBarry Unfortunately it's not filtering. 

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

    Anonymous ,

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

     

    • 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 )
)
  • technolog's avatar
    technolog
    Icon for Super User rankSuper User
    2 years ago

    I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

    Your insights and updates will greatly assist others who might be encountering the same challenge.