cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PhotoBiker
Advocate I
Advocate I

DISTINCTCOUNT with filters

 

Hi,

 

I'm trying to get a DISTINCTCOUNT after I filtered a table. In the table below, I'm only interested in the records where Answer = Yes. Then, I want to count the distinct clients. 

 

chart.png

 

The number of distinct clients should work in any filter context, i. e.:

Overall: 2 clients (A and C)

 

By year:  2015: 1; 2016: 1; 2017: 1

 

By city: New York: 1, Moscow: 1

 

I tried the following code, but it's not considering the filter context. Instead, I always get the total number of distinct clients, no matter the filter context:

 

Clients who answered Yes:=
CALCULATE (
DISTINCTCOUNT ( Table[Client] ),
FILTER(Table,Table[Answer] = "Yes"
))

 

I'd really appreciate some help here 🙂 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hey @PhotoBiker,

 

I tried your code and it worked for me. I got a "Clients who Answered Yes" of 1 for all of the specific filters, and 2 if no filters were selected. Did you create a measure or a calculated column? A measure should produce the proper output while a calculated column would behave the way you are describing.

 

Thanks,

Parker

View solution in original post

6 REPLIES 6
gkeune
Frequent Visitor

I use just measures and is still doent work. 

New count = CALCULATE(DISTINCTCOUNT(Sheet1[name]), FILTER('Calendar', [new] = "new"))
new = IF( [Last 365 days1]-[Last 90 days1]= 0,"New")
Last 90 days1 = CALCULATE(SUM(Sheet1[amount ]),DATESBETWEEN(Sheet1[date],MAX([Date])-90,MAX([Date])))
Last 365 days1 = CALCULATE(SUM(Sheet1[amount ]),DATESBETWEEN(Sheet1[date],MAX([Date])-365,MAX([Date])))
 
I also combined all in one new statement it still doent work. Hope u can help.
Anonymous
Not applicable

Hey @PhotoBiker,

 

I tried your code and it worked for me. I got a "Clients who Answered Yes" of 1 for all of the specific filters, and 2 if no filters were selected. Did you create a measure or a calculated column? A measure should produce the proper output while a calculated column would behave the way you are describing.

 

Thanks,

Parker

View solution in original post

Hey @Anonymous,

 

OMG, you just saved my day 🙂 I'm not sure if it's actually funny or embarrassing...

 

I created a measure, but my data model is fairly complex and there's quite a number of tables that are not related to each other. When I created my measure, I picked the client from an intellisense suggestion - but I chose an unrelated table, that's why the counts were always the same.

 

Thank you very much for double checking my code, and for confirming that it actually works! Based on that I reviewed my actual code and spotted the error.

 

Much appreciated!

Anonymous
Not applicable

@PhotoBiker

 

Awesome, glad to hear it! I like to create a Measures Table where all of my measures are attached. They can get chaotic pretty quick

Hi. 

I have similar situation. Scenario:

Table A: 

1. Employee Name-column

 

Table B:

1. Country

 

Objective: Count distinct Employee Name by Germany and France- ONLY. 

 

  • Below is what I have done.
Measure = calculate(DISTINCTCOUNT('tABLE A'[Employee Name]; FILTER('Table B';'Table B (2)'[Country]="Germany" && Table B (2)'[Country]="France.
  • Error: Too many arguments passed to DISTINCTCOUNT function.Maximum argument count for the function is 1. 
Can anyone help define the write function?
 
Thank you in advance. 

Hey @TaniaR 

Could you try this code?

 

Measure =

   CALCULATE(

     DISTINCTCOUNT('tABLE A'[Employee Name]);

     FILTER('Table B';'Table B (2)'[Country]="Germany" && 'Table B (2)'[Country]="France")

)

 

 

I added an extra ' to Table B (2) and " to France. And also some closing brackets.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.