distinct values letters equal to 2 digit country code

[B00m3r]

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.

[v-huizhn-msft]

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,
Angelia

[B00m3r]

Can you give me an example of the dax count values based on what you are suggesting here please?

[v-huizhn-msft · Accepted solution]

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

[Greg_Deckler]

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.