SUM with Multiple Filters with Ordering

[lynnpowerbi]

Hi, 

I've been struggling to come up with a piece of DAX code that will filter in order. 

I currently have a data table that has duplicate Case IDs with each Case ID having different process outcome date times. What I'm trying to calculate is the Total Errors per Case ID. This would mean it will need to follow the order of 1. filtering on a group of case IDs that share the same ID and then 2. filtering on the MIN process outcome date row and finally 3. Totalling the number of errors associated with the case ID.

I tried to create a calculated column below but it didn't work. Should I create a calculated table or a measure instead? 

Total Errors per Case ID = 

var caseidvar = Table[caseid]
return

var minprocessouttcomeddate = CALCULATE(MIN(Table[processoutcomedatetime]), FILTER(Table[caseid]))
return 

    CALCULATE(
    SUM(Table[Category 1 Errors])
    + SUM(Table[[Category 2 Errors])
    + SUM(.......)
   ),
    FILTER(Table, Table[caseid] = caseidvar && Table[processoutcomedatetime] =  minprocessouttcomeddate)
    )

 I tried another piece of DAX code that led to double counting the errors (e.g if Case ID 123 has three rows with three different process outcome dates and 2 errors showing up, it returned as 6 when it should be 2).

The reason why I am using MIN for process date time is because sometimes the same case can have different number of errors recorded depending on the time stamp.  

Your help would be very much appreciated!! Thank you in advance

[Anonymous]

Hi @lynnpowerbi ,

According to your statement, I think you want to count the error for each Case ID. I think the table currently should look like ([Case ID]/[Category 1 Errors]/[Category 2 Errors]...).

I suggest you to try UNPIVOT function to transform your table. Select all column with format [Category N Errors] and unpivot them.

For reference:

Unpivot columns

Then you just need to distinctcount for each ID to get result.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lynnpowerbi]

Hi Rico, 

Thanks for your reply. My raw dataset is currently set up this way - 

Case ID	Process outcome date time	Error 1.1	Error 1.2	Error 2.1	Error 2.2
100	1/1/2023		1	1
100	2/1/2023		1
101	3/1/2023	1			1
101	4/1/2023	1

I have at least 20+ error code columns in raw data. Should I unpivot the error code columns first or should I start grouping the error categories first? 

My end goal is

1. to be able to calculate total errors for Case 100 to be 2 and Case 101 to be 2 (following the criteria that we only want Min Process Outcome Date Time) 

2. Similiarly, to be able to calculate the # error categories for Case 100 and 101 to be 2.  

Thank you