Calculate SUM with multiple criteria from different columns

[lynnpowerbi]

Hi 

I have a dataset that looks like follows:

Case ID   ProcessDateTime        Process2.0DateTime     Error Codes     Value

100       2/01/2023               1/01/2023              1.01             0
100       2/01/2023               1/01/2023              1.02             1
100       2/01/2023               4/03/2023              1.01             0
100       2/01/2023               4/03/2023              1.02             0
100       2/01/2023               3/03/2023              1.01             1     
100       2/01/2023               3/03/2023              1.02             0                    

Note: There are duplicate Case IDs and there are more rows with different ProcessDateTimes. 

I have made 2 calculated columns where

1. MinProcessDateTime is the EARLIEST Date recorded for a CaseID - in this case it is 2/01/2023, however this list is larger so there are more dates captured here.

2. The Proc2LastStage looks at the LATEST Date recorded for Process 2.0Date with the condition that the date is EARLIER than the MinProcessDateTime - meaning the result is 1/01/2023 as there are only two dates that are before 2/01/2023 (1/01/2023 and 12/12/2022). 

Case ID   ProcessDate   Process2.0Date  Error Codes  Value  MinProcessDateTime   Proc2LastStage

100       2/01/2023          1/01/2023     1.01        0      2/01/2023          1/01/2023
100       2/01/2023          1/01/2023     1.02        1      2/01/2023          1/01/2023
100       2/01/2023          4/03/2023     1.01        0      2/01/2023          1/01/2023
100       2/01/2023          4/03/2023     1.02        0      2/01/2023          1/01/2023
100       2/01/2023          12/12/2022     1.01        1      2/01/2023         1/01/2023
100       2/01/2023          12/12/2022     1.02        0      2/01/2023         1/01/2023
                 

What i need to solve for is the # Errors based on the two criteria above and for each unique case ID.

This means I need a table in the Visualisation/Report tab that can show me 

Case ID  #Errors

100            1

I was unsure what steps to take so I created a calculated table to show only Distinct Case IDs with their respective MinProcessDateTime and Proc2LastStage using the formula below

CalcTable= 

SUMMARIZECOLUMNS(FactTable[caseid], "MinProcessDatetime", MIN(FactTable[MinProcessDateTime]), "Last Stage2", MAX(FactTable[Proc2LastStage]))

Then I tried to do a measure 

# Errors M = 

VAR caseidvar = 
VALUES('CalcTable'[caseid])

VAR firstveroutcomedate =
VALUES('CalcTable'[MinProcessDateTime])

VAR laststagedate = 
VALUES('CalcTable'[Proc2LastStage])

RETURN


     CALCULATE(
     SUM(FactTable[Value]),
  'FactTable'[caseid] IN caseidvar
    && 'FactTable'[ProcessOutcomeDate] IN firstveroutcomedate
    && 'FactTable'[Proc2LastStage] IN laststagedate, 
  CROSSFILTER('CalcTable'[caseid], FactTable[caseid], Both) 
)  

But this didn't seem to give the right results, and when I put it on a vis, it doesn't seem to interact with any date filters, etc.

I don't know if it's necessary to duplicate MinProcessDateTime and Proc2LastStage in a separate table, I was just trying to create a column of distinct Case IDs. 

I would really appreciate your help!

Maybe it's a matter of SUMX?  But how do you SUMX on duplicate CaseIDs and where values in one Column should = Column2 (e.g. ProcessDateTime = MinProcessDateTime & Process2.0Date = Proc2LastStage)? 

Thanks so much in advance

[johnt75]

Try

# Errors =
CALCULATE (
    SUM ( FactTable[Value] ),
    FactTable[ProcessDateTime] = FactTable[MinProcessDateTime]
        && FactTable[Process2.0Date] = FactTable[Proc2LastStage]
)

[lynnpowerbi]

Hi @johnt75 ,

I'm not sure if this is evaluated against each case ID. 

There are duplicate Case IDs in the dataset

Thanks so much

[johnt75]

It would be evaluated against each row which matches the criteria, i.e. ProcessDateTime = MinProcessDateTime and Process2.0Date = Proc2LastStage. In the sample data you provided it would match the first 2 rows only,giving a sum of 1.

[lynnpowerbi]

Thank you @johnt75 however there may be instances where another case ID has the same process date time and proces2date time stamps.

Is there a way in which I can ensure I am looking at unique case IDs?

I am currently matching on another attribute - Person who worked on the case which is then matched to the segment/department they are in. The final visualisation will show something like the below - 

Segment 1               31/03/22     30/04/22

Number of Errors      330                456

Segment 2 

Number of Errros      345                 843

Hence why I believe I will need to ensure Case IDs should be considered in the formula but I just don't know how

[johnt75]

I think this should do it

# Errors =
SUMX (
    VALUES ( FactTable[Case ID] ),
    CALCULATE (
        SUM ( FactTable[Value] ),
        FactTable[ProcessDateTime] = FactTable[MinProcessDateTime]
            && FactTable[Process2.0Date] = FactTable[Proc2LastStage]
    )
)