Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
makabirarif
Frequent Visitor

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

2 ACCEPTED SOLUTIONS
ERD
Super User
Super User

@makabirarif ,

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

ERD_0-1693498075093.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

@makabirarif , 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 )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

6 REPLIES 6
technolog
Super User
Super User

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.

ERD
Super User
Super User

@makabirarif ,

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

ERD_0-1693498075093.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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

@makabirarif , 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 )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

JoeBarry
Solution Sage
Solution Sage

Hi @makabirarif 

 

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

Thanks @JoeBarry Unfortunately it's not filtering. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.