cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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
1 ACCEPTED SOLUTION
Super User

@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 16
Super User

@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``````
Super User

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``````
Helper III

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)

Super User

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

Helper III

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

Super User

@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``````
Helper III

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

Super User

What is the direction of this relationship?

Helper III

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```

Super User

Hi @JC2022

``````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``````
Helper III

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

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

Super User

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

Helper III

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.

Super User

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

Helper III

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.

Helper III

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors