Forum Discussion

JC2022's avatar
JC2022
Icon for Helper III rankHelper III
3 years ago
Solved

add extra condition to formula

Hi,

How do I add an extra condition to the formula below? I need only to calculate the TT_SCHEDDAY_ITM[Hours] if in another table (which has a relationship) a column has a certain value.

 

Scheduled Hours =
SUMX (
    TT_EMP_SCHED,
    SUMX (
        FILTER (
            TT_SCHEDDAY_ITM,
            TT_SCHEDDAY_ITM[Schedule ID] = TT_EMP_SCHED[Schedule ID]
                && TT_SCHEDDAY_ITM[Date] >= TT_EMP_SCHED[From Date]
                && TT_SCHEDDAY_ITM[Date] <= TT_EMP_SCHED[To Date]
                && NOT ( TT_SCHEDDAY_ITM[Date] IN VALUES ( TT_HOLIDAYS[TT_DATE] ) )
        ),
        TT_SCHEDDAY_ITM[Hours]
    )
) + 0
  • tamerj1's avatar
    tamerj1
    3 years ago

    JC2022 
    Final solution is as follows

    Scheduled Hours = 
VAR T1 =
GENERATE ( 
    FILTER ( 
        SUMMARIZE ( 
            TT_EMP_SCHED,
            TT_EMP_SCHED[Schedule ID],
            TT_EMP_SCHED[From Date],
            TT_EMP_SCHED[To Date],
            TT_EMP[Employee ID],
            TT_EMP[TT_EMPCAT]
        ),
        TT_EMP[TT_EMPCAT] = 51
    ),
    SELECTCOLUMNS ( 
        FILTER ( 
            TT_EMP_CONTRACT,
            TT_EMP_CONTRACT[Employee ID] = TT_EMP[Employee ID]
                && TT_EMP_CONTRACT[TT_BOOKHOURS] = 1
        ),
        "@ContractStart", TT_EMP_CONTRACT[Contract from date],
        "@ContractEnd", TT_EMP_CONTRACT[Contract to date]
    )
)
RETURN
    SUMX (
        T1,
        SUMX (
            FILTER (
                TT_SCHEDDAY_ITM,
                TT_SCHEDDAY_ITM[Schedule ID] = TT_EMP_SCHED[Schedule ID]
                    && TT_SCHEDDAY_ITM[Date] >= TT_EMP_SCHED[From Date]
                    && TT_SCHEDDAY_ITM[Date] <= TT_EMP_SCHED[To Date]
                    && TT_SCHEDDAY_ITM[Date] >= [@ContractStart]
                    && TT_SCHEDDAY_ITM[Date] <= [@ContractEnd]
                    && NOT ( TT_SCHEDDAY_ITM[Date] IN VALUES ( TT_HOLIDAYS[TT_DATE] ) )
            ),
            TT_SCHEDDAY_ITM[Hours]
        )
    ) + 0

16 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    JC2022 
    What is the name of the other table and column? What type of relationship? What is the condition that need to be applied?

    • JC2022's avatar
      JC2022
      Icon for Helper III rankHelper III
      3 years ago

      tamerj1 

      Employee table with Employee category column. 

      Employee table one-to-many with TT_EMP_SCHED table. Linked on Employee ID in both tables.

      The calculation only need to be applied for the Employee ID's with Employee category column "1".

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        JC2022 
        You might need to change the 1 to "1" depending on the tata type of the column

        Scheduled Hours =
SUMX (
    TT_EMP_SCHED,
    SUMX (
        FILTER (
            TT_SCHEDDAY_ITM,
            TT_SCHEDDAY_ITM[Schedule ID] = TT_EMP_SCHED[Schedule ID]
                && TT_SCHEDDAY_ITM[Date] >= TT_EMP_SCHED[From Date]
                && TT_SCHEDDAY_ITM[Date] <= TT_EMP_SCHED[To Date]
                && NOT ( TT_SCHEDDAY_ITM[Date] IN VALUES ( TT_HOLIDAYS[TT_DATE] ) )
                    && RELATED ( Employee[Category] ) = 1
        ),
        TT_SCHEDDAY_ITM[Hours]
    )
) + 0
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi JC2022 
    I believe there is no need to filter per start and end dates. Please try

    Scheduled Hours =
VAR T1 =
    CALCULATETABLE (
        VALUES ( TT_EMP_CONTRACT[Employee ID] ),
        TT_EMP_CONTRACT[TT_BOOKHOURS] = 1
    )
RETURN
    SUMX (
        FILTER (
            TT_EMP_SCHED,
            TT_EMP_SCHED[Employee ID]
                IN T1
                    && RELATED ( TT_EMP[TT_EMPCAT] ) = 1
        ),
        SUMX (
            FILTER (
                TT_SCHEDDAY_ITM,
                TT_SCHEDDAY_ITM[Schedule ID] = TT_EMP_SCHED[Schedule ID]
                    && TT_SCHEDDAY_ITM[Date] >= TT_EMP_SCHED[From Date]
                    && TT_SCHEDDAY_ITM[Date] <= TT_EMP_SCHED[To Date]
                    && NOT ( TT_SCHEDDAY_ITM[Date] IN VALUES ( TT_HOLIDAYS[TT_DATE] ) )
            ),
            TT_SCHEDDAY_ITM[Hours]
        )
    ) + 0
    • JC2022's avatar
      JC2022
      Icon for Helper III rankHelper III
      3 years ago

      tamerj1 

      I still do not get the expected results.

      There is an existing report built with direct query and I would like to change this report into a import built report.

      I am trying to recreate these direct queries into 2 measures for the hours calculation. Where TT_SYS_DAYS = replaced by a Calendar table in my datamodel. Please see my data model below.

       

      Direct query 1 (internal employee, based on e.TT_EMPCAT = 51):

      SELECT sum(si.TT_HOURS)) AS 'ScheduledHours',
      'SI' as Label FROM
      TT_EMP e LEFT JOIN TT_EMP_ORG eo ON (e.TT_EMP_ID = eo.TT_EMP_ID)LEFT JOIN
      TT_ORG o ON (O.TT_ORG_ID = eo.TT_ORG_ID)LEFT JOIN
      TT_EMP_SCHED es ON (e.TT_EMP_ID = es.TT_EMP_ID)LEFT JOIN
      TT_SCHED s ON (s.TT_SCHED_ID = es.TT_SCHED_ID)LEFT JOIN
      TT_SCHEDDAY_ITM si ON (s.TT_SCHED_ID = si.TT_SCHED_ID)LEFT JOIN
      TT_SYS_DAYS sd ON (sd.TT_DATE = si.TT_DATE)LEFT JOIN
      TT_EMP_CONTRACT ec ON (e.TT_EMP_ID = ec.TT_EMP_ID)
      WHERE
      sd.TT_DATE <= eo.TT_TODATE AND
      sd.TT_DATE >= eo.TT_fromDATE AND
      (coalesce(eo.TT_TYPE, 0) = 0) AND
      sd.TT_DATE <= es.tt_todate AND
      sd.TT_DATE >= es.tt_fromdate AND
      sd.TT_DATE <= ec.TT_TODATE AND
      sd.TT_DATE >= ec.TT_fromDATE AND
      NOT EXISTS (SELECT TT_HOLIDAYS.tt_date FROM tt_holidays WHERE TT_HOLIDAYS.tt_date = si.TT_DATE) AND
      ec.TT_BOOKHOURS = 1 AND
      e.TT_EMPCAT = 51
      GROUP BY
      e.TT_EMP_ID,
      o.TT_ORG_ID,
      sd.TT_DATE

       

      Direct query 2 (external employee, based on e.TT_EMPCAT = 52):

      SELECT h.TT_EMP_ID, h.TT_DATE, h.TT_HOURS, 'AE' as Label FROM TT_HRS h
      LEFT JOIN TT_EMP_CONTRACT ec ON
      (ec.TT_EMP_ID = h.TT_EMP_ID AND ec.TT_FROMDATE <= h.TT_DATE AND ec.TT_TODATE >= h.TT_DATE)LEFT JOIN
      tt_org o ON o.TT_ORG_ID = h.TT_ORG_ID LEFT JOIN
      tt_emp e ON e.TT_EMP_ID = h.TT_EMP_ID
      WHERE
      e.TT_EMPCAT = 52 AND
      NOT h.tt_act_id in (15, 67)

       

       

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    JC2022 
    Final solution is as follows

    Scheduled Hours = 
VAR T1 =
GENERATE ( 
    FILTER ( 
        SUMMARIZE ( 
            TT_EMP_SCHED,
            TT_EMP_SCHED[Schedule ID],
            TT_EMP_SCHED[From Date],
            TT_EMP_SCHED[To Date],
            TT_EMP[Employee ID],
            TT_EMP[TT_EMPCAT]
        ),
        TT_EMP[TT_EMPCAT] = 51
    ),
    SELECTCOLUMNS ( 
        FILTER ( 
            TT_EMP_CONTRACT,
            TT_EMP_CONTRACT[Employee ID] = TT_EMP[Employee ID]
                && TT_EMP_CONTRACT[TT_BOOKHOURS] = 1
        ),
        "@ContractStart", TT_EMP_CONTRACT[Contract from date],
        "@ContractEnd", TT_EMP_CONTRACT[Contract to date]
    )
)
RETURN
    SUMX (
        T1,
        SUMX (
            FILTER (
                TT_SCHEDDAY_ITM,
                TT_SCHEDDAY_ITM[Schedule ID] = TT_EMP_SCHED[Schedule ID]
                    && TT_SCHEDDAY_ITM[Date] >= TT_EMP_SCHED[From Date]
                    && TT_SCHEDDAY_ITM[Date] <= TT_EMP_SCHED[To Date]
                    && TT_SCHEDDAY_ITM[Date] >= [@ContractStart]
                    && TT_SCHEDDAY_ITM[Date] <= [@ContractEnd]
                    && NOT ( TT_SCHEDDAY_ITM[Date] IN VALUES ( TT_HOLIDAYS[TT_DATE] ) )
            ),
            TT_SCHEDDAY_ITM[Hours]
        )
    ) + 0