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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
ADSL
Post Prodigy
Post Prodigy

How to Calculate the Distinct Count of Customer with Specific Number/Word

Hi BI Team,

 

I am trying to calculate the distinct count of customer code column without/exclude the specific the number/name below.

 

Without/Exclude the value in customer code column have:

- 9999...

- 29100...

- 1075....

 

Coverage.jpg

 

Please kindly advise the measure of this calcualtion.

 

Source Link - https://drive.google.com/drive/folders/1h4G65DGngL5oYU6JadAA8PbdyowuaxSn?usp=sharing 

1 ACCEPTED SOLUTION

So sorry if I dont understand your requeriment correctly.
The following measure will exclude those codes that START with the values you say:

Dist_Count Coverage = 
CALCULATE(
    DISTINCTCOUNT(Coverage[CUST_CODE]),
    LEFT( Coverage[CUST_CODE], 4 ) <> "9999"
        && LEFT( Coverage[CUST_CODE], 5 ) <> "29100"
        && LEFT( Coverage[CUST_CODE], 4 ) <> "1075"
)

Hope that helps!

View solution in original post

7 REPLIES 7
PabloDeheza
Solution Sage
Solution Sage

Hi there!

Have you tried:

CALCULATE(
    DISTINCTCOUNT( YourTable[CustomerCode] ),
    NOT( YourTable[CustomerCode] IN { Code1, Code2 } )
)

Let me know if that helps!

Hi @PabloDeheza ,

 

I am trying your measure but it seem not working. Please help to check the screenshot below.

 

2022-09-19_22-46-47.jpg

Can you provide the error message? Thanks

The problem is you are treating the code column as an integer and it is a string, add double quotes to the numbers and it should work.

CALCULATE(
    DISTINCTCOUNT( YourTable[CustomerCode] ),
    NOT( YourTable[CustomerCode] IN { "Code1", "Code2" } )
)

Hi @PabloDeheza ,

 

After trying your measure, it's still include the these customer code below.

- 99999...

- 1075...

- 29100

Please kindly check and advise again.

 

For Dist_Count answer correctly in excel is:

 

2022-09-19_23-35-47.jpg

For your measure in BI is:

 

2022-09-19_23-38-00.jpg

So sorry if I dont understand your requeriment correctly.
The following measure will exclude those codes that START with the values you say:

Dist_Count Coverage = 
CALCULATE(
    DISTINCTCOUNT(Coverage[CUST_CODE]),
    LEFT( Coverage[CUST_CODE], 4 ) <> "9999"
        && LEFT( Coverage[CUST_CODE], 5 ) <> "29100"
        && LEFT( Coverage[CUST_CODE], 4 ) <> "1075"
)

Hope that helps!

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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