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

[ADSL]

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....

Please kindly advise the measure of this calcualtion.

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

[PabloDeheza]

Hi there!

Have you tried:

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

Let me know if that helps!

[ADSL]

Hi @PabloDeheza ,

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

[PabloDeheza]

Can you provide the error message? Thanks

[ADSL]

Hi @PabloDeheza,

Please kindly check the screenshot or file - https://drive.google.com/drive/folders/1h4G65DGngL5oYU6JadAA8PbdyowuaxSn?usp=sharing

[PabloDeheza]

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" } )
)

[ADSL]

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:

For your measure in BI is:

[PabloDeheza · 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!