topic Re: DAX query countdistinct in DAX Commands and Tips

DAX query countdistinct

calimero48 — Thu, 16 Jan 2020 10:03:21 GMT

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

Re: DAX query countdistinct

az38 — Fri, 17 Jan 2020 10:51:03 GMT

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

Re: DAX query countdistinct

calimero48 — Sat, 18 Jan 2020 20:00:53 GMT

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

Re: DAX query countdistinct

az38 — Sat, 18 Jan 2020 22:55:41 GMT

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

Re: DAX query countdistinct

calimero48 — Mon, 27 Jan 2020 17:39:40 GMT

Thanks you for your solution, it's working with powerbi but i need it on SSRS.

I have done

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