topic Re: Help me to write the DAX formula using Filter Function to Link Tables in DAX Commands and Tips

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

Anonymous — Thu, 31 Aug 2023 12:45:00 GMT

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

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)

Date	ID	Team	Department	Division
30/06/2023	100	XYZ	MNO	OPQ
31/05/2023	100	ZXY	MNO	OPQ
30/04/2023	100	ZXY	LOM	OPQ

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.?

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

JoeBarry — Thu, 31 Aug 2023 12:52:45 GMT

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

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

Anonymous — Thu, 31 Aug 2023 14:17:27 GMT

Thanks JoeBarry Unfortunately it's not filtering.

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

ERD — Thu, 31 Aug 2023 16:08:05 GMT

Anonymous ,

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

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

Anonymous — Fri, 01 Sep 2023 08:37:12 GMT

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

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

ERD — Fri, 01 Sep 2023 09:53:29 GMT

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

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

technolog — Sun, 24 Sep 2023 08:01:06 GMT

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.