Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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 🙂
Solved! Go to Solution.
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
I use just measures and is still doent work.
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
Hi
Can someone highlight the error in my syntax please?
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!
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.
Hey @Anonymous
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.
Thank you, I have just used this and it worked great 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
110 | |
109 | |
94 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |