cancel
Showing results for
Did you mean:
Helper I

## [DAX] How to combine DISTINCTCOUNT with multiple filters on different columns?

Dear Forum,

it would be great if anyone could bring me on the right track with the following issue.

I have a table similar to this table 'Sales':

 Deal ID Deal Closed Deal Won Reseller 001 FALSE FALSE A 002 TRUE FALSE B 002 TRUE FALSE B 003 FALSE FALSE A 004 TRUE FALSE B 005 TRUE TRUE A 005 TRUE TRUE A 006 TRUE TRUE B

Now, I want to know, how many deals each Reseller has lost.
A deal is lost, when the following conditions apply:

1. [Deal Closed] = TRUE
2. [Deal Won] = FALSE

The twist here: Some deals (here 'Deal ID' 002 and 005) are double in the table, but each 'Deal ID' should only be counted once!

The result should look like:

Attempt to solve this challenge so far:

In case, each [Deal ID] would only occur once in the table, then the following Measure works:

``````COUNT_Reseller_Lost =
CALCULATE (
COUNT ( 'Sales'[Reseller] ),
FILTER (
'Sales',
'Sales'[Deal Closed] = TRUE()
&& 'Sales'[Deal Won] = FALSE()
)
)``````

But how to filter out the deals with double entry?

For only counting each deal per reseller once without the conditions 'Deals Closed' and 'Deals Won', I've found this measure, which might work:

``````Distinct_Count_Deal_ID =
CALCULATE(
DISTINCTCOUNT('Sales'[Deal ID]),
ALLEXCEPT('Sales','Sales'[Reseller])
)``````

But how to combine both?

Thank you very much!

Mister_T

3 ACCEPTED SOLUTIONS
Memorable Member

One way to combine them:

``````COUNT_Reseller_Lost =
CALCULATE (
DISTINCTCOUNT ( 'Sales'[Deal ID] ),
FILTER (
ALLEXCEPT ( 'Sales', 'Sales'[Reseller] ),
'Sales'[Deal Closed] = TRUE ()
&& 'Sales'[Deal Won] = FALSE ()
)
)``````

Edit: DISTINCTCOUNT ( 'Sales'[Reseller] ), --> DISTINCTCOUNT ( 'Sales'[Deal ID] ),

Helper I

Wow, that was quick! Thanks a lot @AlexisOlson !

I assume, in your measure was a small typo and it is supposed to be "DISTINCTCOUNT ( 'Sales'[Deal ID] )" instead of "DISTINCTCOUNT ( 'Sales'[Reseller] )".

``````COUNT_Reseller_Lost =
CALCULATE (
DISTINCTCOUNT ( 'Sales'[Deal ID] ),
FILTER (
ALLEXCEPT ( 'Sales', 'Sales'[Reseller] ),
'Sales'[Deal Closed] = TRUE ()
&& 'Sales'[Deal Won] = FALSE ()
)
)``````

Tested on the dataset given, it seems to work! 🙂

Memorable Member

You are correct. I missed changing that part when I copied and pasted.

3 REPLIES 3
Memorable Member

One way to combine them:

``````COUNT_Reseller_Lost =
CALCULATE (
DISTINCTCOUNT ( 'Sales'[Deal ID] ),
FILTER (
ALLEXCEPT ( 'Sales', 'Sales'[Reseller] ),
'Sales'[Deal Closed] = TRUE ()
&& 'Sales'[Deal Won] = FALSE ()
)
)``````

Edit: DISTINCTCOUNT ( 'Sales'[Reseller] ), --> DISTINCTCOUNT ( 'Sales'[Deal ID] ),

Helper I

Wow, that was quick! Thanks a lot @AlexisOlson !

I assume, in your measure was a small typo and it is supposed to be "DISTINCTCOUNT ( 'Sales'[Deal ID] )" instead of "DISTINCTCOUNT ( 'Sales'[Reseller] )".

``````COUNT_Reseller_Lost =
CALCULATE (
DISTINCTCOUNT ( 'Sales'[Deal ID] ),
FILTER (
ALLEXCEPT ( 'Sales', 'Sales'[Reseller] ),
'Sales'[Deal Closed] = TRUE ()
&& 'Sales'[Deal Won] = FALSE ()
)
)``````

Tested on the dataset given, it seems to work! 🙂

Memorable Member

You are correct. I missed changing that part when I copied and pasted.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks