Determine common elements from several conditions

[wbart]

Hello,

I want to extract common values and count them. The table looks like this:

name	condition
A	1
B	1
C	1
D	2
A	2
C	2

 As a result I want to have the infomation that names A and C belong to the conditions 1 and 2. I want to count the amount of names which have conditions in common and I want to list (show this elements.) How do I do this?

Best regards

wbart

[v-xiaotang · Accepted solution]

Hi @wbart 

you can try this.

-

create a table

common condition = 
var _tab1=SELECTCOLUMNS(FILTER('Table','Table'[condition]=1),"name1",'Table'[name])
var _tab2=SELECTCOLUMNS(FILTER('Table','Table'[condition]=2),"name2",'Table'[name])
return
INTERSECT(_tab1,_tab2)

then, create the count measure:

count = CALCULATE(DISTINCTCOUNT('common condition'[name1]),ALL('common condition'))

result:

-

OR

you can try this, and set "is not 1"

countDistinct = CALCULATE(DISTINCTCOUNT('Table'[condition]),ALLEXCEPT('Table','Table'[name]))

result:

by the way, if you want the most frequent, you can set TopN for name like bellow

Best Regards,

Community Support Team _ Tang

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

[wbart]

Thanks #v-xiaotang,

I stil try to get this running :). I'll get back to you.

Best regards

wbart

[v-xiaotang]

Hi @wbart 

Have you solved this problem? If yes, could you kindly accept the answer helpful as the solution (or kindly share your solution ). so the others can find it more quickly.
really appreciate！
Any question, please let me know. Looking forward to receiving your reply.

Best Regards,

Community Support Team _Tang

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