cancel
Showing results for
Did you mean:
Helper III

## DAX formula Distinct/Distinctcount

Dear all,

I need to calculate the total number of customers for "Estimated Sales = SALES+FIRM ORDERS+FORECAST".  I applied a formulas DAX to calculate the sum of customers of the 3 categories below, however, the output is not the distinct number of customers for the total Estimated Sales. There are duplicates.  May you please help? Thanks.

NB Customers TO+FO+Frcst =
CALCULATE(
DISTINCTCOUNT(SALES[Customer_Code])
+DISTINCTCOUNT(SALES_FIRM_ORDERS[Customer_Code])
+DISTINCTCOUNT(SALES_FORECAST[Customer_Code])
)

 SALES FIRM ORDERS FORECAST Customer Code Customer Code Customer Code A A B B B C C E F D F G E G H A B C C F H
1 ACCEPTED SOLUTION
Community Champion

Hey @EZV12 ,

you have to combine the columns first and then count the distinct values.

Try the following measure:

``````Amount Distinct Customers =
COUNTROWS (
DISTINCT (
UNION (
VALUES ( 'FIRM ORDERS'[Customer Code] ),
VALUES ( FORECAST[Customer Code] ),
VALUES ( SALES[Customer Code] )
)
)
)``````

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

3 REPLIES 3
Community Champion

Hey @EZV12 ,

you have to combine the columns first and then count the distinct values.

Try the following measure:

``````Amount Distinct Customers =
COUNTROWS (
DISTINCT (
UNION (
VALUES ( 'FIRM ORDERS'[Customer Code] ),
VALUES ( FORECAST[Customer Code] ),
VALUES ( SALES[Customer Code] )
)
)
)``````

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Helper III

Hello Denis,

It works!!  Thank you very much for your help!

Best regards

Rachel

Super User II

@EZV12
Try:

``````Disctinct values =
VAR _Sales =
VALUES ( 'Table'[SALES] )
VAR _FirmOrders =
VALUES ( 'Table'[FIRM ORDERS] )
VAR _Forecast =
VALUES ( 'Table'[FORECAST] )
RETURN
COUNTROWS ( DISTINCT ( UNION ( _Sales, UNION ( _FirmOrders, _Forecast ) ) ) )
``````

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors