Distinct Count not working when exporting to excel

[qmartiny]

Hello lovely people.

I have a matrix visual with the format below

Master Account	Sub Account	Expected Distinct Count of Master
AAA	A_1	1
AAA	A_2	0
BBB	B_1	1
BBB	B_2	0

I have a Card visual that shows the distinct count of Master Accounts. ex Master Accounts = 2 as expected

I have added a column  "Distinct Count of Master Account." so that my user when exporting to excel will see the same count as the card visual.

However exporting the matrix in excel, the distinct count column just shows 1 for each row. I have also tried with a measure instead. No luck

Thank you for your help.

The user is on mac and cannot use the pivot table (add to data model) trick

Quentin

[Anonymous · Accepted solution]

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

[VahidDM]

Hi @qmartiny 

Can you share a sample of your data?

BTW, try this measure:

Measure = CALCULATE(DISTINCTCOUNT('Table'[Master Account]),ALL('Table'))

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/

[qmartiny]

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