topic Re: add extra condition to formula in DAX Commands and Tips

add extra condition to formula

JC2022 — Tue, 29 Nov 2022 16:46:57 GMT

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

Re: add extra condition to formula

tamerj1 — Tue, 29 Nov 2022 16:50:34 GMT

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

Re: add extra condition to formula

JC2022 — Tue, 29 Nov 2022 16:58:17 GMT

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".

Re: add extra condition to formula

tamerj1 — Tue, 29 Nov 2022 17:13:07 GMT

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

Re: add extra condition to formula

JC2022 — Tue, 29 Nov 2022 22:08:29 GMT

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

Re: add extra condition to formula

tamerj1 — Wed, 30 Nov 2022 03:37:04 GMT

JC2022

What is the direction of this relationship?

Re: add extra condition to formula

JC2022 — Wed, 30 Nov 2022 07:59:55 GMT

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

Re: add extra condition to formula

tamerj1 — Wed, 30 Nov 2022 09:02:37 GMT

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

Re: add extra condition to formula

JC2022 — Wed, 30 Nov 2022 10:41:02 GMT

tamerj1

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

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

Re: add extra condition to formula

tamerj1 — Wed, 30 Nov 2022 11:55:49 GMT

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? 🙂

Re: add extra condition to formula

JC2022 — Wed, 30 Nov 2022 14:47:41 GMT

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.

Re: add extra condition to formula

tamerj1 — Wed, 30 Nov 2022 19:04:45 GMT

JC2022

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

Re: add extra condition to formula

JC2022 — Wed, 30 Nov 2022 19:18:27 GMT

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.

Re: add extra condition to formula

JC2022 — Wed, 30 Nov 2022 20:15:19 GMT

tamerj1

please let me know if you need more info.

Re: add extra condition to formula

tamerj1 — Thu, 01 Dec 2022 09:24:58 GMT

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

Re: add extra condition to formula

JC2022 — Thu, 01 Dec 2022 10:24:35 GMT

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)

Re: add extra condition to formula

tamerj1 — Sun, 04 Dec 2022 20:16:17 GMT

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