DAX measure to exclude specific value from count

[Anonymous]

Hello all:

I have the following scenario:

- Process can result in multiple outcomes based on user selection (or lack of):  "Requested", "Yes", "No", "Next".

- Every process has at the minimum the "Requested" outcome

- Processes can have a maximum of 4 outcomes (e.g. Requested, Next, Next, Yes or Requested, Next, Next, No).

The dataset looks something like this:

I'm able to create measures to count unique processes that result in "Yes" or "No" or those where the only outcome is "Requested".  

I cannot come up with a way to calculate the number of distinct process ID which only have "Next" as an outcome (i.e. the user selected Next once or twice but on the last step never selected "Yes" or "No" (rows 13-15 on the screenshot).  

Any idea?  Any help appreciated.

Thanks

Eric

[AlB · Accepted solution]

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?    

[v-cherch-msft · Accepted solution]

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

[Anonymous]

Thank you @v-cherch-msft and @AlB. 

I tried both solutions and they work equally well.  I didn't know about the "EXCEPT" method, very handy. I think I'll go with Cherie's solution though because the LastOutcome measure can become the foundation for all the other measures i need, in a much more elegant way than I was previously doing.  

Cheers and thanks!

Eric

[AlB · Accepted solution]

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?    

[Anonymous]

Thanks @AlB.  Your solution works well.  

To answer your question, it's a little easier to count outcomes of Yes and No since in my scenario there can only be one Yes or one No per Process ID (whereas there can be 2 Next).  So basically I was doing a simple filter:

Count_Yes = 
CALCULATE(
	COUNTA('Table1'[outcome]), FILTER('Table1', 'Table1'[Outcome] = "YES") ,
	ALL('Table1'[Date])
)

Thanks also for the tip on posting sample data in tabular format.

Cheers,

Eric