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

       

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