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

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago
Sales Measure =
CALCULATE (
    SUM ( 'Table'[Sales] ),
    ALLEXCEPT ( 'Table', 'Table'[Customer Name], 'Table'[Month] )
)

Customers (>$0) Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer Name] ),
    FILTER ( ALLSELECTED ( 'Table' ), [Sales Measure] > 0 )
)
mrpauld's avatar
mrpauld
Frequent Visitor
9 years ago

Excellent! Thank you so much. Worked perfectly. 

 

In a nutshell, just for my understanding -- what exactly is going on here? 

 

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

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

 

I tried to research how this works, but am struggling. Thanks again!