Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dynamic count the occurrence based on slicers

Dear everyone,

I have dataset with the below structure:

FactoryPersonType
Factory 3person 100Type 1
Factory 3person 100Type 16
Factory 3person 100Type 6
Factory 3person 101Type 1
Factory 3person 101Type 21
Factory 3person 101Type 22
Factory 3person 101Type 23
Factory 3person 101Type 6
Factory 3person 102Type 1
Factory 3person 102Type 16
Factory 3person 102Type 23
Factory 3person 102Type 6
Factory 3person 103Type 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

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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)

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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