DAX query countdistinct

0
votes

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

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

2
dax
The number of distinct vendors is 2, which is the column you're summarizing. If you want customers, summarize over 'orders'[Customer] instead. Also you can use the DATE-function instead of VALUE. Makes the code easier to read; DATE(2019;01;10) In addition, in the NotBilled calculation you seem to have the same date in the two filters. – OscarLar
Sorry I have made an error on NotBilled formula (I have changed). The count is on Vendor column – calimero48
If you post the desired output it will be easier to help you. At the moment I don't understand what the question/problem is. – OscarLar
I have added the desired output – calimero48

2 Answers

0
votes

Ok, not sure how you want to calculate SumSales, but I guess you can figure out how to add that column by extending this code:

NewTable = 
SUMMARIZE(
    'orders';
    [vendor];
    "NbCustomerDistinct"; DISTINCTCOUNT('orders'[Customer])
    /*"SumSales = Add calculation to get SumSales here*/
)
0
votes

I have found a way to do it not sure that is the best,

EVALUATE 
VAR InternalTable = 
    SUMMARIZECOLUMNS( 
        'orders'[vendor],
        "Total",CALCULATE(SUM('Orders'[Price]),
               'orders'[OrderBilling] >= VALUE ( "01/01/2019" ),
               'orders'[OrderBilling] <= VALUE ( "10/01/2019" ))
            +CALCULATE(SUM('Orders'[Price]]),
               'orders'[OrderBilling] > VALUE ( "10/01/2019" ),
                'orders'[OrderTaken]   <= VALUE ("10/01/2019" ))
            )

Return
GROUPBY(
    InternalTable,
    'orders'[vendor],
    "Total_Sales",SUMX(CURRENTGROUP(),[Total]),
    "Current_Clients",COUNTX(CURRENTGROUP(),[Total])
)