topic Re: Distinct Count one column with Contained Strings from another in DAX Commands and Tips

Distinct Count one column with Contained Strings from another

JCuoco — Wed, 05 Jan 2022 22:16:42 GMT

Team,

I would like to do a distinct count on a column (Total Encounters) but only count the encounters that also have the value of "No Show" in a separate column

Thanks,

James

Re: Distinct Count one column with Contained Strings from another

ValtteriN — Wed, 05 Jan 2022 22:27:04 GMT

Hi,

Here is one way to do this:

Dax:

Apples =

calculate(COUNT(Applecount[AppleCount]),ALL(Applecount)) //Calculate all fruits

CALCULATE(DISTINCTCOUNT(Applecount[AppleCount]),

iserror(SEARCH("Apple",Applecount[AppleCount])),ALL(Applecount[AppleCount])) //calculate NOT apples

End result:

I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

Re: Distinct Count one column with Contained Strings from another

VahidDM — Wed, 05 Jan 2022 23:08:57 GMT

Hi JCuoco

You need to use Calculation with filter:

If you want to filter the table to show the rows which have "No Show" in a seperate column:

Measure =
Calculate(DISTINCTCOUNT(table[Total Encounters]),Filter(table,table[separate column]="No Show"))

If "No Show" is part of the values of data in seperate column, use ValtteriN solution.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: Distinct Count one column with Contained Strings from another

JCuoco — Thu, 06 Jan 2022 17:21:46 GMT

VahidDM thank you! This is absolutely perfect! love it! i did the "LONG" way last night and got the same number, so i know it is correct! THANK YOU