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

 

  • calimero48's avatar
    calimero48
    Helper II
    6 years ago

    Hi

     

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