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 month, that have sales > 0. Where I run into an issue is when the sales in aggregate for one customer equals 0 as it's not considering the sum of all the rows for the one customer. As is, my current measure is Count  Customers=CALCULATE(COUNTDISTINCT[Customer Name]),[Sales]>0))

 

Example:

 

Customer Name Sales Month
XYZ Sailing 100 1/1/2017
XYZ Sailing 100 1/1/2017
XYZ Sailing -100 1/1/2017
XYZ Sailing -100 1/1/2017
XYZ Sailing 50 1/1/2017
XYZ Sailing -50 1/1/2017

How would I get COUNTDISTINCT to evaluate the rows in aggregate versus row by row. Is there any easy way to accomplish this without a customer table? 

 

Thank you!

 

 

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

4 Replies

  • Sean's avatar
    Sean
    Community 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!

      • 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