RELATEDTABLE and double FILTER

[TonyMcA]

This function below works in that it returns the values that I expect from creating a new column in ‘ResourceTimePhasedDataSet’ called ‘Demand’ that is calculated from a relationship to a 1 to many table named ‘Assignments’ by filtering on [AssignmentStartDate] (from ‘Assignments’) being <= [TimeByDay] from ‘ResourceTimePhased DataSet’

Demand = sumx(FILTER(RELATEDTABLE(Assignments),[AssignmentStartDate]<=[TimeByDay]),(Assignments[Av Per Day]))

However the values I want also need to be filtered by [TimeByDay] <=[AssignmentFinishDate]. I can build the logic to do this by example using an AND statement as below:

Demand = sumx(FILTER(RELATEDTABLE(Assignments),AND([AssignmentStartDate]<=[TimeByDay]),[TimeByDay]<=[AssignmentFinishDate]),(Assignments[Av Per Day]))

BUT – Power BI DAX doesn’t like this – “Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2”.

Any suggestions?

Regards, Tony

[radpir · Accepted solution]

hi Tony,

it seems that you misplaced some of the round brackets in the formula:

Demand = sumx(FILTER(RELATEDTABLE(Assignments),AND([AssignmentStartDate]<=[TimeByDay]),[TimeByDay]<=[AssignmentFinishDate]),(Assignments[Av Per Day]))

it should be:

Demand =
    SUMX (
        FILTER (
            RELATEDTABLE ( Assignments ),
            AND (
                [AssignmentStartDate] <= [TimeByDay],
                [TimeByDay] <= [AssignmentFinishDate]
            )
        ),
        Assignments[Av Per Day]
    )

regards,

radpir

[TonyMcA]

Thanks Radpir,

That's great - very helpful!

All the best, Tony