Forum Discussion

mrpauld's avatar
mrpauld
Frequent Visitor
9 years ago
Solved

COUNTDISTINCT on an Aggregation

Hello,   I've written a simple COUNTDISTINCT mesaure that runs into issues when in aggregate, the totals of a customer rows equals zero.   I want to count the number of distinct customers in a mo...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi mrpauld,

     

    Step 1- Removing all filter context from Customer field and Month field

     

    Using Allexcept doesn't mean to remove all filter context from Customer field and Month field. If we want to remove filters from any column but only a few from a table, we can use ALLEXCEPT. In other words, we group the rows based on Customer and Month field, while regardless other field context.

    Reference: ALL, ALLEXCEPT and VALUES in DAX
                     Using ALLEXCEPTED versus ALL and VALUES

     

    Step 2- We're layering in an ALLSELECTED on the FILTER function.... why?

     

    Based on my test, if we remove ALLSELECTED here, the measure still works. 

    Customers (>$0) Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer Name] ),
    FILTER ( 'Table' , [Sales Measure] > 0 )
)

     

    To get more detailed introduction about AllSelected function, please refer to: Understanding AllSelected

     

    Best regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi mrpauld,

 

Step 1- Removing all filter context from Customer field and Month field

 

Using Allexcept doesn't mean to remove all filter context from Customer field and Month field. If we want to remove filters from any column but only a few from a table, we can use ALLEXCEPT. In other words, we group the rows based on Customer and Month field, while regardless other field context.

Reference: ALL, ALLEXCEPT and VALUES in DAX
                 Using ALLEXCEPTED versus ALL and VALUES

 

Step 2- We're layering in an ALLSELECTED on the FILTER function.... why?

 

Based on my test, if we remove ALLSELECTED here, the measure still works. 

Customers (>$0) Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer Name] ),
    FILTER ( 'Table' , [Sales Measure] > 0 )
)

 

To get more detailed introduction about AllSelected function, please refer to: Understanding AllSelected

 

Best regards,
Yuliana Gu

mrpauld's avatar
mrpauld
Frequent Visitor
9 years ago

Thanks Yuliana, I actually did end up removing ALLSELECTED and it worked. Thanks for your explantion on the measure!