Formula to evaluate several pieces of information and count the resulting number of times?

[jmscrx]

Hi folks, have one that is a bit over my head as a beginner to Dax and Power BI. I have data that looks like the following.

Control Number	ICT Record Name	Created Date	Status	CA Testing Results
CRX-2019-1	Control Test-429	7/1/2020 12:00	COMPLETE	Operating as Expected
CRX-2019-1	Control Test-899	1/1/2021 12:01	ASSIGNED
CRX-2019-10	Control Test-399	7/1/2020 12:00	COMPLETE	Operating with Findings: Corrective Action not Required
CRX-2019-10	Control Test-869	1/1/2021 12:01	COMPLETE	Operating as Expected
CRX-2019-10	Control Test-942	2/1/2021 12:01	NOT_ASSIGNED
CRX-2019-100	Control Test-931	1/1/2021 12:01	NOT_ASSIGNED	Not Operating as Expected: Corrective & Preventative Action Required
CRX-2019-102	Control Test-936	1/1/2021 12:01	NOT_ASSIGNED
CRX-2019-104	Control Test-947	1/1/2021 17:01	IN_PROGRESS
CRX-2019-104	Control Test-1055	1/27/2021 18:16	NOT_ASSIGNED
CRX-2019-11	Control Test-882	1/1/2021 12:01	NOT_ASSIGNED	Incomplete: Additional Evidence Required

What I'd like to be able to do is create a measure (or whatever is similar and most appropriate) to calculate the total number of unique controls (Control Number column) where the most recent control test that is marked COMPLETE (Created Date and Status columns) has a result (CA Testing Results column) of either Not Operating as Expected... or Operating with Findings... Please note that a control can have multiple outstanding control tests (ICT Record Name column), which is why I need to ensure I'm checking the status of all tests for a control of being COMPLETE then of those, determing the most recent one and including it in my count based on the requirements for the CA Testing Results column as stated above.

As an example, if we look at control CRX-2019-1 (first 2 rows), there are 2 outstanding control tests. Of those, only one is COMPLETE, so that's the only one I would consider however the results are Operating as Expected so I would not count anything in my final count here. However if we look at CRX-2019-10, which has 3 outstanding control tests. 2 of these tests are marked as COMPLETE so I would evaluate those and of those, the most recent is from 1/1/21, however the results from this one are Operating as Expected so I would not count this either. Had the results been either Not Operating as Expected... or Operating with Findings... then I would have counted this in my overall count.

Hopefully this makes sense, thanks!

[Anonymous]

// The model must have a date table, Dates,
// that will be connected to the fact table
// T on Dates[Date] = T[Created Date].

// According to Best Practices all fields in
// the fact table should be hidden and slicing
// must be performed by dimensions only.

// This measure will respond to any selections
// in any dimensions. If you want to suppress
// honoring any filters, you'll have to adjust
// the measure. I don't do it since I can't
// figure out from your description if you need
// that. For instance, if you slice by Status =
// "Assigned", then the measure will return BLANK
// since there can't be any entries with a status of
// "complete" found in the current context.

[Your Measure] =
var vControlNumsWithLatestCreatedDate =
    CALCULATETABLE(
        ADDCOLUMNS(
            DISTINCT( T[Control Number] ),
            // Get the most recent date in
            // the current context.
            "@MostRecentDate",
                CALCULATE(
                    MAX( T[Created Date] )
                )
        ),
        KEEPFILTERS( T[Status] = "complete" )
    )
var vControlNumsAboveWithAppropriateResult =
    CALCULATETABLE(
        T,
        TREATAS(
            vControlNumsWithLatestCreatedDate,
            T[Control Number],
            T[Created Date]
        ),
        KEEPFILTERS(
            T[CA Testing Results] in {
                "not operating as expected",
                "operating with findings"
            }
        )
    )
var Result =
    COUNTROWS( vControlNumsAboveWithAppropriateResult )
return
    Result

[ERD]

Hello @jmscrx ,

Here is an option you can try:

#Measure = 
COUNTROWS(
    FILTER (
        GENERATE (
            SUMMARIZECOLUMNS ( 'Table'[Control Number], 'Table'[Status] ),
            CALCULATETABLE (
                TOPN (
                    1,
                    SUMMARIZE (
                        'Table',
                        'Table'[Created Date],
                        'Table'[CA Testing Results]
                    ),
                    'Table'[Created Date]
                ))),
        'Table'[CA Testing Results] <> "Operating as Expected"
            && 'Table'[Status] = "COMPLETE"
    )
)

[Anonymous]

Hi @ERD 

Your measure is certainly wrong as it does not adhere to the specification as put forward in the description.