cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

1 ACCEPTED SOLUTION
Solution Sage

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!

7 REPLIES 7
Solution Sage

Hi there!

Have you tried:

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

Let me know if that helps!

Post Prodigy

Hi @PabloDeheza ,

Solution Sage

Can you provide the error message? Thanks

Post Prodigy
Solution Sage

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" } )
)``````
Post Prodigy

Hi @PabloDeheza ,

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

- 99999...

- 1075...

- 29100

For Dist_Count answer correctly in excel is:

For your measure in BI is:

Solution Sage

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!