Dynamic count the occurrence based on slicers

[Anonymous]

Dear everyone,

I have dataset with the below structure:

Factory	Person	Type
Factory 3	person 100	Type 1
Factory 3	person 100	Type 16
Factory 3	person 100	Type 6
Factory 3	person 101	Type 1
Factory 3	person 101	Type 21
Factory 3	person 101	Type 22
Factory 3	person 101	Type 23
Factory 3	person 101	Type 6
Factory 3	person 102	Type 1
Factory 3	person 102	Type 16
Factory 3	person 102	Type 23
Factory 3	person 102	Type 6
Factory 3	person 103	Type 1

I use Factory and Type as filters. And I want to count number of unique persons with the selected types.

For example, if I select Type as "Type 1", "Type 22", "Type 23" then there is a measure that shows the number of persons which 3 above types only.

Here is the file

http://www.mediafire.com/file/kf9pg8d9wz7kjnw/Count_the_filtered_only.pbix/file

Can you please help me out?

Thank you in advance!

Best regards,

Cindy

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft]

Hi, @Anonymous 

Based on your description, you may create a measure as below.

Result = 
var _num = 
CALCULATE(
    DISTINCTCOUNT(Sheet4[Type]),
    ALLSELECTED(Sheet4)
)
var tab = 
SUMMARIZE(
    Sheet4,
    Sheet4[Person],
    "flag",
    IF(
      DISTINCTCOUNT(Sheet4[Type])=_num,
      1,0
    )
)
return
CALCULATE(
    DISTINCTCOUNT(Sheet4[Person]),
    FILTER(
        tab,
        [flag]=1
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , do you having all three types. Because having one of the three is the default behavior

Try like

measure =
var _cnt = countx(allselected(Table), Table[Type])
return
countx(filter(summarize(table, table[person], "_1", countrows(Table)),[_1]=_cnt),[person)