Count distinct items that have a specific value in another column but have ALSO had another value

[Anonymous]

I haven't found this in a search so maybe I'm asking it wrong or thinking about it incorrectly.  I have a data set with 2 columns (simplified) and I want to count the number of "Items" (rows) that have a specific value in the "Sprint" column, but ONLY count it if that "Item number" ALSO has a different value in the "Sprint" column.  Here is a simplified dataset

Item Number	Sprint
1061511	Backlog
1061511	Sprint 23
1061511	Sprint 24
1061511	Sprint 25
1061511	Sprint 26
1104217	Sprint 22
1104217	Sprint 23
1210684	Backlog
1210684	Sprint 24
1210685	Sprint 25

The logic seem to me:  Count "Item Number", if it's Sprint value is "Sprint 25" BUT ONLY if "Item number" also has a value that is NOT "Sprint 25" and is also not "Backlog".   My count should be 2.  Item number 1061511 and 1210685 meet the criteria, 1104217 never has a "Sprint 15" value so it isn't counted.

Hope this did not double post - hit POST the last time I typed it up but it didn't seem to go through.
Any help is appreciated

[Icey]

Hi @Anonymous ,

Please check if the attachment is helpful.

Count =
VAR SelectedSprint =
    SELECTEDVALUE ( 'Table'[Sprint] )
VAR CurrentSprint =
    MAX ( 'Table'[Sprint] )
VAR t =
    ADDCOLUMNS (
        'Table',
        "Sprint_",
            IF (
                COUNTROWS (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Item Number] = EARLIER ( 'Table'[Item Number] )
                            && 'Table'[Sprint] = SelectedSprint
                    )
                ) = 1
                    && COUNTROWS (
                        FILTER (
                            ALL ( 'Table' ),
                            'Table'[Item Number] = EARLIER ( 'Table'[Item Number] )
                                && 'Table'[Sprint] <> "Backlog"
                                && 'Table'[Sprint] <> SelectedSprint
                        )
                    ) >= 1,
                1
            )
    )
RETURN
    COUNTROWS ( FILTER ( t, [Sprint_] = 1 ) )

Best regards

Icey

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Thank you for the reply.  It is currently not working and showing a "blank".
When I try each individual filter in the "IF" statement I get "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."  So I will continue to tweak it and see if I can get it to work.

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please share your .pbix, removing sensitive information, for test.

Best Regards,

Icey