DAX Distinct Count Question

[cr731]

I'm trying to calculate a distinct count of customers and having some difficulty; my table is like this,

Customer	Sales Team	Col1	Col2	Col3
abc	123	xx	xx	xx
def	456	xx	xx	xx
abc	789	yy	yy	yy

<tbody>
</tbody>

So what I am looking for is the distinct count of Sales Team by Customer, but I want the measure to respect any filters applied to Col1, Col2, Col3.

So if I have a PivotTable showing customer abc with no other filters, then it should show 2. But if I have a slicer that is slicing Col1's value of 'yy' out, then it should be just 1.

So far I have:

=
[SIZE=1][COLOR=#0000ff][SIZE=1][COLOR=#0000ff]CALCULATE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1]([/SIZE][SIZE=1][COLOR=#0000ff][SIZE=1][COLOR=#0000ff]DISTINCTCOUNT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1]( [Sales Team] ),[/SIZE][SIZE=1][COLOR=#0000ff][SIZE=1][COLOR=#0000ff]ALLEXCEPT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=1](Sales,Sales[Customer]))[/SIZE]
[SIZE=1][/SIZE]

But this would still return 2 even if I filter out 'yy'.

Is there an easy solution?

Thanks

 

[citizenbh]

And to try:

Measure:CountTeam:=DISTINCTCOUNT( [Sales Team] )

In Pivot Table Fields:

Rows: Sales Team
Σ VALUES: CountTeam
FILTERS: Costumer, Col1...

 

[akice]

Yes, just a measure with DISTINCTCOUNT ( Sales[Sales Team] ) should be all you need.

Your ALLEXCECPT function call was removing all the column filters on the 'Sales' table except for any filters on the 'Customer' column which is why you were still getting a 2 in your example above. Even though you were slicing 'yy', the ALLEXCEPT turned around and removed the 'yy' column filter.

 

[cr731]

Sorry I should have stated this originally but I want the measure to work even if I have Sales Team in my Rows, like this:

Rows:
Sales Team
Customer

What I'm really looking for is to look at specific customers within a given sales team, and know whether that customer is also serviced by another sales team, so I think just DISTINCTCOUNT won't let me put Sales Team into the rows like that and still give me the total number of distinct sales team per customer.

 

[FranzV]

You can try

= CALCULATE ( DISTINCTCOUNT ( Sales[Sales Team] ), ALL ( Sales[Sales Team] ) )

This should only override the filter on Sales Team, keeping any other filters active.

I hope it helps.

*Note: I recommend you check the Rules for DAX Code Formatting from The Italians. There are some key aspects like always including the table name when you are referencing a column and never including it when you reference a measure.