Forum Discussion

Warner's avatar
Warner
New Member
4 years ago
Solved

Measure to count multiple text value from one column

Hi All, 

 

I am trying to create a measure or measures, which can give me an overall figure for each text value that appears within my dataset. 

 

I have one column that has a range of text values in it. Which summaries some factors that i am trying to count. 

 

For example: 

 

Client IDFactors
1100000000AA
1100000001AA, AB
1100000002AA, AB, AC
1100000003AA

 

What i am trying to do is create a measure that can count all of these in one go. Instead of what i am doing now which would be: 

 

Test = CALCULATE(COUNTROWS('Contact data'),FILTER('Contact data','Contact data'[Factors]="AA"))
Test = CALCULATE(COUNTROWS('Contact data'),FILTER('Contact data','Contact data'[Factors]="AA, AB"))
 
and then creating a new measure to add these up. 
 
This is becasue the data has 655 rows and i dont want to have to create a measue every time a new option within the text field changes? 
 
So from the above example i would want the total for the measure to be 3? 
 
 
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Warner ;

    You could modify it by the following:

    Test = CALCULATE(COUNTROWS('Contact data'),FILTER(ALL('Contact data'),'Contact data'[Factors] in {"AA","AA, AB"}))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Warner ;

    You could modify it by the following:

    Test = CALCULATE(COUNTROWS('Contact data'),FILTER(ALL('Contact data'),'Contact data'[Factors] in {"AA","AA, AB"}))

    The final output is shown below:

    Best Regards,
    Community Support Team_ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.