Power Pivot - Relatedtable function using a filter

Question

My tables are called IW37Ntab, IW38tab and IW47tab.

I

n power pivot I've made IW47tab the main one. I've added some columns that pull information from the other table using =RELATED function and this has worked. The issue I now have is that I need to pull some information through using a filter.

IW37tab has a list of Orders with Tasks - ie Order 1234 Task 10, 20, 30 each task has estimated hours against it

IW47tab has a list of Orders with a number of hours relating to the Tasks - ie Order 1234 Task 10 may have x5 lines against it.

On the Power Pivot data modal my table is as follows (Blank columns in are IW47, columns with a formula in is info pulled from another table using a formula)

Order	Task	Discipline	UOM	IW38 Planned costs	IW38 Total costs	IW38 Estimated costs	IW37 Estimated hours
				using =RELATED(IW38tab[Planned Costs])	using =RELATED(IW38tab[Total Costs])	using =RELATED(IW38tab[Estimated costs])

I’m looking for the formula to put in IW37 Estimated hours column using a filter ie if the Task on IW47 is 10 it looks at the task column on the IW37 report and pull through the estimated hours associated to the task. We’ve tried using this formula =SUMX(FILTER(RELATEDTABLE(IW37Ntab),[Task]=[Task]),[Estimated hrs]) but it is returning the below #ERROR message.

Does anyone know that the formula or function would be for this. Any help would be greatly appreciated.

Answer 1

https://docs.google.com/spreadsheets/d/1hQXlSLOc3eQdVKJ2qkkFupgQ0y0NAd_a/edit?usp=drive_link&ouid=107061616491750512396&rtpof=true&sd=true

Please see above a link to my spreadsheet. I have added a tab called Example of Pivot Table this give an example of what I would like my pivot table to look like.

I would be mainly looking at the top level information is is FL 4.

many thanks

Answer 2

I apologize for the confusion earlier. Given the change in column names, you would need to adjust the formula accordingly. Here's how you can modify the formula:

=CALCULATE(
    SUM(IW37Ntab[Estimated hrs]),
    FILTER(
        ALL(IW37Ntab),
        IW37Ntab[OP] = EARLIER(IW47tab[Activity])
    )
)

In this formula, IW37Ntab[OP] corresponds to the Task column in your IW37Ntab table, and IW47tab[Activity] corresponds to the Task column in your IW47tab table.

This formula calculates the sum of the Estimated hrs field from IW37Ntab for each activity in IW47tab. The EARLIER function is used to refer to the earlier row context

Please replace IW37Ntab[Estimated hrs], IW37Ntab[OP], and IW47tab[Activity] with your actual column names if they're different.

If you're still encountering an error, it could be due to a type mismatch or a lack of relationship between the tables based on the OP and Activity fields. In such cases, I recommend reaching out to Microsoft Support or a Power Pivot expert for further assistance. They have access to more resources and can provide more personalized help.

Regards Adeyemi

Answer 3

Hi,

Share the download link of the file and show the expected result there.

Answer 4

Thank you very much for taking the time to help.

I'm still getting a #ERROR message but a different one this time. One the main workbook the column names have now be changed to match a different report.

IW47 task column is now Activity

IW37 task column is now OP

thanks,

Answer 5

Hello

I’m Adeyemi and I’d be happy to help you with your question.

The error message you're seeing is likely due to a type mismatch in your DAX formula. The RELATEDTABLE function returns a table of values, and the FILTER function expects a table as its first argument

In your formula, you're comparing [Task] from IW37Ntab with [Task] from IW47tab. If these two fields are not of the same data type, or if there's no relationship between these two tables based on the Task field, you might encounter issues

Here's a revised version of your formula that might work:

=CALCULATE(
    SUM(IW37Ntab[Estimated hrs]),
    FILTER(
        ALL(IW37Ntab),
        IW37Ntab[Task] = EARLIER(IW47tab[Task])
    )
)

This formula calculates the sum of the Estimated hrs field from IW37Ntab for each task in IW47tab. The EARLIER function is used to refer to the earlier row context

Please replace IW37Ntab[Estimated hrs], IW37Ntab[Task], and IW47tab[Task] with your actual column names if they're different.

I hope this helps

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below

Regards Adeyemi