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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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