Forum Discussion

calimero48's avatar
calimero48
Icon for Helper II rankHelper 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 ...
az38's avatar
az38
Icon for Community Champion rankCommunity 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
Icon for Helper II rankHelper 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])
)