DAX measure to exclude specific value from count

Hi Anonymous

It's always best to post your data in text/tabular format in addition to a screen capture. People trying to help can then readily copy the sample data and run some tests if they need to.

Let's see if I've understood correctly.

If every process ID has "Request" at the very least and you are interested in the ones that have only "Next" additionally, that means that you are looking for process IDs that do not have "Yes" or "No". So we could try by first selecting IDs that have "Next" and then "subtracting" those that have "Yes" or "No". We can conveniently do that with the EXCEPT( ) function:

IDsWithOnlyNext =
VAR _IDsWithNext =
    CALCULATETABLE ( DISTINCT ( Table1[Process ID] ), Table1[Outcome] = "Next" )
VAR _IDsWithYesOrNo =
    CALCULATETABLE (
        DISTINCT ( Table1[Process ID] ),
        Table1[Outcome] IN { "Yes", "No" }
    )
RETURN
    COUNTROWS ( EXCEPT ( _IDsWithNext, _IDsWithYesOrNo ) )

Note that in the second set we'll also potentially have (if that's possible, I'm not sure) IDs with only  "Requested" and  "Yes" or "No", i.e. without "Next". That shouldn't be a problem since those won't be in the first set.

Now, I am curious. You say you already have measures to count unique processes that result in "Yes" or "No" or those where the only outcome is "Requested". How did you approach those, since the logic for them would seem quite similar to that of the measure you couldn't come up with?    

Hi Anonymous

I would suggest you create a measure to get the last outcome for each process ID.Then you may get the count if the [LastOutcome]="Next".For example:

LastOutcome =
CALCULATE (
    SELECTEDVALUE ( Table1[Outcome] ),
    FILTER (
        Table1,
        Table1[Index]
            = MAXX (
                FILTER (
                    ALL ( Table1 ),
                    Table1[Process ID] = SELECTEDVALUE ( Table1[Process ID] )
                ),
                Table1[Index]
            )
    )
)

Count = COUNTROWS(FILTER(Table1,[LastOutcome]="Next"))

Regards,

Cherie