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
