[HELP] Distinct Count Measure with criteria, and responds to slicer selection

[kbandito]

Hi, I have been trying to build a measure to distinct count Person with a certain criteria, and have that responding to a slicer that selects Place.

For example, I have a full table below ('ALL').

If i select Place AAA and BBB only using slicer, there is 9 total visits by 6 persons. Of this 6 persons, Person '1' has visited both places, while the remaining 5 persons have visited either one of AAA and BBB.

I want a measure that is able to distinct count people like Person '1'.

ALL			SELECTION
Person	Place		Person	Place
1	AAA		1	AAA
1	BBB		1	BBB
1	CCC		1	BBB
1	DDD		2	AAA
2	AAA		2	AAA
2	CCC		3	BBB
3	BBB		4	AAA
3	CCC		5	BBB
3	DDD		7	AAA
2	AAA
1	BBB
4	AAA
5	BBB
6	CCC
7	AAA

[TomMartens]

Hey @kbandito ,

I'm not sure what the expected result should look like, but maybe this measure provides what you are looking for:

count distinct person = 
CALCULATE(
    DISTINCTCOUNT('Table'[Person])
    , ALLSELECTED('Table'[Person])
)

At least it allows to create visuals like below:

Regards,

Tom

[kbandito]

Hi @TomMartens , thanks.

However, instead of a total count of person who have been to either one of the places, i want to only count person who have been to both places.

[TomMartens · Accepted solution]

Hey @kbandito ,

this measure

Measure = 
var _noofplaces = COUNTROWS(ALLSELECTED('Table'[Place]))
return
SUMX(
    VALUES('Table'[Person])
    , IF(CALCULATE(DISTINCTCOUNT('Table'[Place]), ALLSELECTED('Table'[Place])) >= _noofplaces , 1 , BLANK())
)

allows to create these visuals:

Hopefully, this is what you are looking for.

Regards,

Tom