DAX formula Distinct/Distinctcount

[EZV12]

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

[selimovd · Accepted solution]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[EZV12]

Hello Denis,

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

Best regards

Rachel

[PaulDBrown]

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