Forum Discussion

wbart's avatar
wbart
Icon for Helper I rankHelper I
5 years ago
Solved

Determine common elements from several conditions

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...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
5 years ago

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's avatar
wbart
Icon for Helper I rankHelper I
5 years ago

Thanks #v-xiaotang,

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

Best regards

wbart

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    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.