Forum Discussion

calimero48's avatar
calimero48
Helper II
6 years ago

DAX query countdistinct

I want to countdistinct from the following in DAX.

I have a table orders with the columns and sample as:

OrderNumber  Customer Vendor   OrderTaken   OrderDelivery   OrderBilling    Qty   Price
1              1        V1     2018/12/01    2019/01/10     2019/01/10       1      1
2              10       V1     2019/01/04    2019/01/07     2019/01/07       1      1
3              100      V2     2019/01/02    2019/01/04     2019/01/06       1      1
4              100      V2     2019/01/02    2019/07/01     2019/07/01       1      1
(              10       V1     2020/01/04    2020/01/07     2020/01/07       1      1

Date Format is yyyy/MM/dd

I want to count the price and count the number of distinct customer for each vendor from the 1st January to the 10th January billed and the ordertaken before the 10th Januray that are not billed.

I am able to calculate the sum for the price, one for billed order and one for ordertaken but I need to count the number of distinct client even if there is an order billed and one not yet billed.

For the sum I have done:

EVALUATE
SUMMARIZECOLUMNS (
    'orders'[vendor],
    "Billed", CALCULATE (
        SUM ( 'Orders'[Price] ),
        'orders'[OrderBilling] >= VALUE ( "01/01/2019" ),
        'orders'[OrderBilling] <= VALUE ( "10/01/2019" )
    ),
    "NotBilled", CALCULATE (
        SUM ( 'ZBW00001'[CANET(-DATION)] ),
        'orders'[OrderBilling] > VALUE ( "20190110" ),
        'orders'[OrderTaken]   <= VALUE ( "20190110" )
    )
)

I got as result

Vendor     Billed        NotBilled
V1            2
V2            1              1

The number of distinct customer is 2 (Vendor).

I do not see how can I get it. If somebody can help me. Thanks in advance

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi calimero48 

    simple DISTINCTCOUNT isn't ok for you?

    The number of distinct customer = 
CALCULATE(DISTINCTCOUNT('orders'[Vendor]);ALL('orders'))

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

    • calimero48's avatar
      calimero48
      Helper II
      6 years ago

      Hi

       

      Thank you for your help but it's not so simple.

       

      The desired output is to have the number of distinct customer from the two periods:

               'orders'[OrderBilling] >= VALUE ( "01/01/2019" ), 'orders'[OrderBilling] <= VALUE ( "10/01/2019" ) and

               'orders'[OrderBilling] > VALUE ( "20190110" ), 'orders'[OrderTaken] <= VALUE ( "20190110" )

       

      If I used the same method as the SUM, i will have

      Vendor NbcustomerBilled NbCustomerNotBilled

            V1             2

            V2             1                               1

       

      and the number of distinct user will be 4 that is not the truth, should be 3.

       

      Desired output

       

      Vendor NbCustomerDistinct SumSales

           V1               2                          2  ( Two customers 1 and 10)

           V2               1                          2 ( Only one customer 100 )

       

      Thanks in advance for your help

       

      • az38's avatar
        az38
        Community Champion
        6 years ago

        calimero48 

        thats what I have done:

        1. create a table

        Table 2 = 
UNION(
FILTER(ALL('Table');
            'Table'[OrderBilling]>=DATE(2019;01;01) && 'Table'[OrderBilling]<=DATE(2019;01;10));
FILTER(ALL('Table');
            'Table'[OrderBilling]>DATE(2019;01;10) && 'Table'[OrderTaken]<=DATE(2019;01;10)
)
)

        then add to visual a column 'Table2'[Customer] and set aggregation as Count(Distinct) in visual settings. This is Number of customers

        then add to visual a column 'Table2'[Customer] and set aggregation as Count in visual settings. This is Sum of sales by customers

         

         

        do not hesitate to give a kudo to useful posts and mark solutions as solution

        LinkedIn