Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mister_T
Advocate I
Advocate 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
Super User
Super User

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
Super User
Super User

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] ),

 

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.