Forum Discussion

Vivek26's avatar
Vivek26
Icon for Advocate I rankAdvocate I
2 years ago
Solved

Need help with a Measure without creating calculated columns

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 ...
  • Dangar332's avatar
    Dangar332
    2 years ago

    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's avatar
Kishore_Kadhir
Icon for Resolver II rankResolver II
2 years ago
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