Need help with a Measure without creating calculated columns

[Vivek26]

I have a scenario where I need to get the sum of specific rows if they follow a particular path. 

We need to calculate the sum of rows(column Hours) with DEPT_NAME "HR" only if it also went through the DEPT "OPS" for the same order.

For example we should consider the Orderid 1 and 3 because those order had both HR and OPS while 2 is not considered because it didn't had OPS.

Now we also need to calculate the sum of hours for these subset of orders that fullfill the above criteria ("HR" with OPS) .

Sample Data and snapshow below , Can you please help me with this ?

ID	Date	Order ID	DEPT NAME	Hours
1	22-12-2023	1	OPS	10
2	24-12-2023	1	FIN	12
3	29-12-2023	1	HR	14
4	11-12-2023	2	FIN	20
5	20-12-2023	2	HR	30
6	14-12-2023	3	FIN	30
7	20-12-2023	3	OPS	40
8	20-12-2023	3	HR	50

[Dangar332 · Accepted solution]

hi, @Vivek26 

try below measure 

Measure 2 = 
CALCULATE(
     SUM('Table (3)'[hours]),
     FILTER(
        'Table (3)',
        'Table (3)'[dept name] in {"hr","ops"} && 
        'Table (3)'[order id]=MAX('Table (3)'[order id])
    )
)

code for month-year

[Kishore_Kadhir]

Hi @Vivek26 ,

See if the below measure works:

TotalHourswithHRandOPS =

var concatenation = CONCATENATEX(VALUES(Dept[DeptName]), Dept[DeptName], ",")

var output = SUMMARIZE(Dept, Dept[OrderID], "Dept", SWITCH(TRUE(),

                                                    CONTAINSSTRING(concatenation, "OPS") &&

                                                    CONTAINSSTRING(concatenation, "HR"), "Yes"))

RETURN

SUMX(FILTER(output, [Dept] <> BLANK()), CALCULATE(SUM(Dept[Hours]), FILTER(Dept, Dept[DeptName] IN {"HR", "OPS"})))

Thanks,

Kishore