Skip to main content
cancel
Showing results for 
Search instead 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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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