add extra condition to formula

[JC2022]

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 · Accepted solution]

@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]

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]

@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]

@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]

@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]

@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

[JC2022]

@tamerj

Is this also possible if I want another condition where a column of a different table (which is connected to the Employee table but not to the TT_EMP_SCHED table) is a certain value?

[tamerj1]

@JC2022 

What is the direction of this relationship?

[JC2022]

@tamerj1 

Please see my model below.

So basically it's the code below. But this should only be applied for the Employee ID with TT_EMP_CONTRACT[TT_BOOKHOURS] = 1. This TT_BOOKHOURS can change over time that's why there is a [Contract from date] and a [Contract to date]. So timeperiod is also important.

The relation between TT_EMP_CONTRACT is many-to-one with TT_EMP on [Employee ID].

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]

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]

@tamerj1 

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

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

[tamerj1]

@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]

@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]

@JC2022 

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

[JC2022]

@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]

@tamerj1 

please let me know if you need more info.