DAX measure that returns the distinct number of values that have duplicates within the table

Question

Fairly new and self-taught with DAX. I run an accuracy log that tracks incoming applications (Application[Application_ID]) and errors committed in processing that application (Error_Log[Application_ID]).

I want to find the number of applications that contain more than one error. For example, if 10 applications have errors, 6 of those applications have 1 error and the rest have 2 or more errors, I want to return the value of 4.

I'm trying to avoid a calculated column (like a "Multiple_Errors" TRUE/FALSE column) as it's refresh times are already longer than I'd like, but if it's unavoidable, it could be accommodated.

We were able to build an Excel formula with SUMPRODUCT for a very high level summary of the information, but I want more granularity than that formula can give me.

The online search has only led to finding articles on how to count the number of duplicates, flag the duplicates, remove duplicates or some other task, where I need to count a distinct number of values that have been duplicated within a table.

I have tried a few different DAX measures, but all of them have yielded incorrect results. For example...

    =
CALCULATE (
    DISTINCTCOUNT ( Error_Log[Appplication_ID] ),
    FILTER ( Error_Log, COUNTA ( Error_Log[Appplication_ID] ) > 1 )
)

Drilling down into this result shows that all of the applications with errors are being pulled over, rather than only those with greater than one error.

After playing with a few options, I haven't been able to find the solution. Any help/pointers/direction would be greatly appreciated!

Answer 1

I think you are looking for something like this:

Measure =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Error_Log,
            Error_Log[Application_ID],
            "count", COUNTROWS ( Error_Log )
        ),
        [count] > 1
    )
)

The SUMMARIZE function returns a virtual summarized table, with the count of each Application_ID in a column called "count". The outer COUNTROWS function then returns the number of rows in the virtual table where [count] is greater then 1.

Answer 2

Your measure is fine and works as defined. Please see the attached screen.

App ID 100 has 4 Type 1 errors, 101 has 2 Type 2 and 1 Type 3 errors but because of the distinct count, they have 1 each.

102 has single Type 3 but we are using Error Type to group the log, Type 3 show two counts (1 each for 102 and 101)

Note that COUNTA ( Error_Log[Appplication_ID] ) > 1 condition has been satisfied for 102 also because of grouping column.

We do not see Type 6 in the pivot table at the right because of COUNTA ( Error_Log[Appplication_ID] ) > 1.

So, although the measure works, we might miss interpreting the result or we might miss to use correct DAX for the requirement.