Forum Discussion
Distinct Count not working when exporting to excel
Hi qmartiny ,
If you want the first row of each Master Account to show the distinct count and other rows show 0, you could create a formula as below.
Column = var _min = CALCULATE(MIN('Table'[BP_ID]),FILTER('Table','Table'[Top ID]=EARLIER('Table'[Top ID]))) var _count = calculate(DISTINCTCOUNT('Table'[Top ID]),FILTER('Table','Table'[Top ID]=EARLIER('Table'[Top ID]))) return IF('Table'[BP_ID]=_min,_count,0)Best Regard,
Jay
Hi qmartiny
Can you share a sample of your data?
BTW, try this measure:
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/
Hello,
Here's the expected result. I want the user to be able to use the column 'Expected Result' as a field in a pivot table to get the distinct count of TOP_ID. It doesn't need to look like this but that's the way I picture a distinct count working
At the moment the Distinct Count built in on PowerBI does not work and writes 1 for each line.
Using CALCULATE(ALLSELECTED) works but gives me the total, repeated for each line. I want to avoid confusion for the user.
They are filtering the data in this table before exporting, hence why I used ALLSELECTED.
Really confused why the built in distinct count is not working.,.,
Thanks for your help
| BP_ID | Top ID | Expected Result |
| 1000 | 9000 | 1 |
| 1001 | 9000 | 0 |
| 1002 | 9000 | 0 |
| 1003 | 8000 | 1 |
| 1004 | 8000 | 0 |
| 1005 | 8000 | 0 |
| 1006 | 8000 | 0 |
| 1007 | 8000 | 0 |
| 1008 | 5000 | 1 |
| 1009 | 5000 | 0 |
| 1010 | 5000 | 0 |