Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
calimero48
Helper II
Helper II

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

4 REPLIES 4
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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
Community Champion
Community Champion

@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

 

Безымянный.png

 

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.