Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
There were a few great responses, but this is the one I tried first - worked like a charm - thanks!!
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
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 ?
@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
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...
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |