Forum Discussion
distinct values letters equal to 2 digit country code
I have a table similar to the below. I have 20,0000 assets from around the world prefixed with country code 2 digits follwed by other reference letters & numbers that indicate city, floor,room etc, each asset name is different, the only consistency is the 2 digit prefix for country. so for example I may have 5,000 with au-XXX, 3,000 with us-XXX and so on. My question is how do I count by first 2 digits only for each 2 digit country?
I have no idea where to start.
Hi B00m3r,
For example, I create a sample table only including [Assert Name] column, then create a calculated column as Greg_Deckler posted.
sample tablecreate a calculated column
If you want to get how many asset name group by first 2 digit country code. Just select the calculated column and measure(count of assert name as row level), you will get expected result as follows.
Best Regards,
Angelia
4 Replies
- Greg_Deckler
Community Champion
Well, the simplest way would be to create a new calculated column with a formula like:
FirstTwo = LEFT([Asset Name],2)
Then the problem is trivial.
v-huizhn-msftMicrosoft Employee
Hi B00m3r,
Create a calculated column to get first two digits as Greg_Deckler posted, then count values based on the new calculated column. Please feel free to ask if you have other issue.
Best Regards,
AngeliaCan you give me an example of the dax count values based on what you are suggesting here please?
- v-huizhn-msft
Hi B00m3r,
For example, I create a sample table only including [Assert Name] column, then create a calculated column as Greg_Deckler posted.
sample tablecreate a calculated column
If you want to get how many asset name group by first 2 digit country code. Just select the calculated column and measure(count of assert name as row level), you will get expected result as follows.
Best Regards,
Angelia
