Getting a table that lists people that have ONLY ONE specific value in a category

[grggmrtn]

I have the following data:

Person ID	Date	Service	Provider
1	04-01-2021	Something	Someone
1	05-01-2021	AnotherThing	SomeoneElse
2	06-01-2021	Something	SomeoneElse
3	07-01-2021	Something	Someone
3	08-01-2021	AnotherThing	SomeoneElse
3	09-01-2021	ThirdThing	ThirdPerson
4	10-01-2021	AnotherThing	Someone
5	11-01-2021	Something	SomeoneElse
6	12-01-2021	Something	ThirdPerson
6	13-01-2021	ThirdThing	Someone
7	14-01-2021	Something	Someone
8	15-01-2021	ThirdThing	Someone

I need a list of the people that have any service provided by "Someone" - but ONLY if they have no other services. So the above example should return Person IDs 4, 7 and 8

Any ideas on how I can achieve this?

[AlB · Accepted solution]

Hi @grggmrtn 

The description of the requirement is a bit ambiguous but try the following:

1. Place Table1[ID] in a table visual. Make sure it is set to Don't summarize so that all individual values are shown.

2. Create this measure

Show measure =
VAR check_ =
    CALCULATE ( COUNT ( Table1[Service] ), Table1[Provider] <> "Someone" ) = 0
        && CALCULATE ( COUNT ( Table1[Service] ), Table1[Provider] = "Someone" ) > 0
RETURN
    IF ( check_, 1, 0 )

 3. Place [Show measure] as a filter to the table visual and choose to show when value is 1

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[grggmrtn]

There were a few great responses, but this is the one I tried first - worked like a charm - thanks!!

[amitchandak]

@grggmrtn ,

Create a measure like

countx(filter(summarize(Table, Table[Person ID], "_1", countdistinct([Provider]), "_2", calculate(countdistinct([Provider]), filter(Table, Table[Provider] ="Someone"))), [_1] =[_2]),[Person ID])

and use with person id in visual

[AilleryO]

Another idea, could be using SUMMARIZECOLUMNS to make count grouped by Provider, and finally check that count of Someone is >0 and other counts=0.

Is that of any help ?

[amitchandak]

@grggmrtn , Create a measure like

countx(filter(summarize(Table, Table[Person ID], "_1", countdistinct([Provider]), "_2", calculate(countdistinct([Provider]), filter(Table, Table[Provider] ="Someone"))), [_1] =[_2]),[Person ID])

and use with person id in visual

[AilleryO]

Not easy to answer without a more detailed context. Can a person ID have multiple lines with Someone ?

If not you can use a COUNT<2 based on Person ID, combined with FILTER function...

Tell us more...

[grggmrtn]

@AilleryOYeah that's an oops on my part. A Person ID CAN have multiple lines with Someone - and as long as it's ONLY Someone, I need them to be among the results. The COUNT <2 doesn't work for exactly this reason.