Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
Solved! Go to 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!
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.
Can you provide the error message? Thanks
Hi @PabloDeheza,
Please kindly check the screenshot or file - https://drive.google.com/drive/folders/1h4G65DGngL5oYU6JadAA8PbdyowuaxSn?usp=sharing
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:
For your measure in BI is:
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!
User | Count |
---|---|
84 | |
80 | |
69 | |
46 | |
46 |
User | Count |
---|---|
105 | |
44 | |
39 | |
39 | |
39 |