Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
Thanks Radpir,
That's great - very helpful!
All the best, Tony
User | Count |
---|---|
93 | |
85 | |
78 | |
68 | |
62 |
User | Count |
---|---|
113 | |
99 | |
96 | |
64 | |
58 |