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!

Announcements

Power BI Monthly Update - July 2024

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

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors