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.   Schedu...
  • 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
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi JC2022 
Please try

Scheduled Hours =
VAR CurrentID = TT_SCHEDDAY_ITM[Schedule ID]
VAR CurrentDate = TT_SCHEDDAY_ITM[Date]
VAR T1 =
    CALCULATETABLE (
        VALUES ( TT_EMP_CONTRACT[Employee ID] ),
        FILTER (
            TT_EMP_CONTRACT,
            TT_EMP_CONTRACT[Contract from date] <= CurrentDate
                && TT_EMP_CONTRACT[Contract to date] >= CurrentDate
                && 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 

It's not working. Please see the error below.

It looks like it's not recognizing the two columns (red underlining).

 

 

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

    JC2022 
    The first one is not used and can be deleted. Regarding the Date from & to, it is not clear how would you like to filter them. Based on what exactly? By the way, how does your report look like? I forgot whether this is a Measure or a calculated column? 🙂

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

    tamerj1 

    Ok I am sorry. It's probably easier if I explain the complete question.

    I would like to calculate the Hours (TT_SCHEDDAY_ITM[Hours]) for each Employee ID (TT_EMP[Employee ID]) and show these for example per month.

    In 2 different measures for Internal and External employees (TT_EMP[TT_EMPCAT]) where Internal = 0 and external = 1.

    These hours only need to be calculated if for this Employee ID the TT_EMP_CONTRACT[TT_BOOKHOURS] = 1. This TT_BOOKHOURS column can change over time so therefore the from and to date in this TT_EMP_CONTRACT table.

    So ultimate end result should be 2 measures. 1 measure for Internal Employee calculated hours and 1 measure for External Employee calculated hours. For External Employee there should be an additional filter on TT_ACT[Activity ID]. Some Activity ID should be excluded from the calculation.

     

     

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

    JC2022 

    Still did not explain how the table shall be filtered based on the start and end dates. 

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

    tamerj1 

    for example: for the period TT_EMP_CONTRACT[Contract from date] to TT_EMP_CONTRACT[Contract to date] where the TT_EMP_CONTRACT[TT_BOOKHOURS] are 0 the TT_SCHEDDAY_ITM[Hours] should not be calculated for this Employee ID. 

    So when last week the TT_EMP_CONTRACT[TT_BOOKHOURS] was 0 this should not be calculated but when the TT_EMP_CONTRACT[TT_BOOKHOURS] this week are 1 it should be included in the calculation.

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

    tamerj1 

    please let me know if you need more info.