Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |