Use DAX to breakdown iOS and Android OS versions

[bschmiedeler]

I have an Azure SQL file that lists mobile devices. One field is the DeviceOS, which is formated as follows:

Android 8.0.0

iOS 12.4.0

Android 7.1.2

and so on.

I created a new column with the following DAX formula: 

DevOSAndroidiOS = If (Left(Device[DeviceOS],3) = "iOS","iOS","Android")

So I can seee the total number of iOS versions and Android versions.

However, I am stumped on how to extract the number of different iOS version, and number of different Android versions. So if I have 100 iOS devices, I would like to know how many iOS 12.x, iOS 11.x, and iOS 10.x versions (we only support iOS version 10.x and above). So if we have 50 iOS 12 and 25 iOS 11 and 25 iOS 10 versions, I want to have those numbers and the percent of the iOS total. Same with Android. 

Cannot figure out how to do this?

Any help would be greatly appreciated.

[Anonymous · Accepted solution]

Hi @bschmiedeler,

My suggestion is:
1) Using PowerQuery;
2) Split column "DeviceOS" by Delimeter;
3) Select "Space" as delimiter;
4) Split at Right-most delimiter;

5) Change the second column type, from "Int64.Type" to "type text"

From:

To: