Forum Discussion

TonyMcA's avatar
TonyMcA
Frequent Visitor
9 years ago
Solved

RELATEDTABLE and double FILTER

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 t...
Need Help
  • radpir's avatar
    radpir
    9 years ago

    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

radpir's avatar
radpir
Icon for Resolver II rankResolver II
9 years ago

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's avatar
    TonyMcA
    Frequent Visitor
    9 years ago

    Thanks Radpir,

     

    That's great - very helpful!

     

    All the best, Tony