topic Re: DISTINCTCOUNT doesn't give correct result as a column in DAX Commands and Tips

DISTINCTCOUNT doesn't give correct result as a column

gspollock — Wed, 10 May 2023 18:13:03 GMT

Hi all,

I want to create a column that takes a distinct count of account_id column for each Account Manager, like this:

Number Accounts Shared = CALCULATE(DISTINCTCOUNT(mbammsreportshare[account_id]))

The column is simply returning the total count for that Account Manager. The same formula in a measure gives the correct results.

Account Manager	Number Accounts Shared (measure)	Number Accounts Shared (column)
Acct Mgr1	1	1
Acct Mgr2
Acct Mgr3	2	3
Acct Mgr4
Acct Mgr5	4	4
Acct Mgr6	1	1
Acct Mgr7	1	1
Acct Mgr8	4	4
Acct Mgr9	6	15

I need to use a column because I want to create a measure that sums Number Accounts Shared like this:

Total Over 3 = CALCULATE(sum( mbammsreportshare[Number Accounts Shared]), FILTER(mbammsreportshare,[Number Accounts Shared]>3 ))

Power BI won't allow a measure in the SUM expression.

A second problem is that summing the column returns blanks when using the filter expression.

If there is a way around using a column for Number Accounts Shared, I would still like to know why does it not work as a column.

Thanks for your help!

Greg_Deckler — Wed, 10 May 2023 17:56:15 GMT

tamerj1 — Wed, 10 May 2023 18:30:40 GMT

and try

Total Over 3 =
SUMX (
VALUES ( mbammsreportshare[Account Manager] ),
INT ( [Number Accounts Shared] > 3 )
)