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:

namecondition

A

1
B1
C1
D2
A2
C2

 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

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.

3 Replies

  • 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.