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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EZV12
Helper IV
Helper IV

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
selimovd
Super User
Super User

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
 

View solution in original post

3 REPLIES 3
selimovd
Super User
Super User

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
 

Hello Denis,

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

Best regards

Rachel

PaulDBrown
Community Champion
Community Champion

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




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.