cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mister_T
Helper I
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 IDDeal ClosedDeal WonReseller
001FALSEFALSEA
002TRUEFALSEB
002TRUEFALSEB
003FALSEFALSEA
004TRUEFALSEB
005TRUETRUEA
005TRUETRUEA
006TRUETRUEB

 

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:

Mister_T_2-1618495992169.png


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
AlexisOlson
Memorable Member
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] ),

 

View solution in original post

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! 🙂

 

View solution in original post

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

View solution in original post

3 REPLIES 3
AlexisOlson
Memorable Member
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] ),

 

View solution in original post

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! 🙂

 

View solution in original post

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors