Filter only for instances when a value is the only one to appear in a given date range

[Flawn]

Hello All,

I am looking to get a distinctcount of values in one column (Service Users), where for a seperate column (Service Type) - only one particular value ('Service Request') appears in the filtered Year-to-Date period. So even if the value does appear for the given Service User, if other values appear other than Service Request, it shouldn't be counted. If the value 'Service Request' appears multiple times, and no other values appear for the given period, that should be counted as well.

Here's a very basic version of the dataset I am working with - i have excluded the dates, though they are relevent; as they function fine and are linked to a date table.

Service User	Service Type
Tim	Service Request
Tim	Intake
Tim	Support
Bill	Service Request
Bill	Support
Mark	Support
Emily	Service Request
Emily	Service Request
Nick	Service Request

In this case, the return value should be '2' - Emily and Nick should be counted. 

Obviously a basic filter won't work here, as it wont exclude those service users who have service types other than 'Service Request'. I've also tried fiddlign with allexcept but that doesn't seem to be the way either.

Thanks in advance for any help you are able to provide!

Warm Regards,
Flawn

[OwenAuger · Accepted solution]

Hi @Flawn 

Here are some options:

# Users with Service Request Only = 
SUMX (
    VALUES ( Data[Service User] ),
    IF ( CALCULATE (  SELECTEDVALUE ( Data[Service Type] ) ) = "Service Request", 1 )
)

# Users with Service Request Only = 
COUNTROWS (
    FILTER (
        VALUES ( Data[Service User] ),
        CALCULATE ( SELECTEDVALUE ( Data[Service Type] ) ) = "Service Request"
    )
)

# Users with Service Request Only = 
VAR ServiceRequestUsers =
    CALCULATETABLE (
        VALUES ( Data[Service User] ),
        KEEPFILTERS ( Data[Service Type] = "Service Request" )
    )
VAR NonServiceRequestUsers =
    CALCULATETABLE (
        VALUES ( Data[Service User] ),
        KEEPFILTERS ( Data[Service Type] <> "Service Request" )
    )
VAR Result =
    COUNTROWS (
        EXCEPT ( ServiceRequestUsers, NonServiceRequestUsers )
    )
RETURN
    Result

For simplicity, I would probably go with the first option.

Do the above work for you?

Regards

[Flawn]

Well done Owen! The first one seems to work at a glance, as does the second. I'll have to do a manual doublecheck to be sure, but I think we're fine.

Thanks again!