Unique Software name Count

[dommyw277]

Is there a way of grouping software and ignoring version numbers in power BI?
As an example we have a sheet full of software eg say Visio 2010, Visio 2014 etc but I want to count the total amount of numbers of the software name?

[audreygerred · Accepted solution]

Hi! I suggest having your software name as two fields in your dim table, one field for Base name and one for the year, then you can do a distinct count on the name.

[AmiraBedh · Accepted solution]

Hello !

If you are willing to use PQ you need to create a col to strip digits and common version punctuation then group on it.

let
    s  = Text.From([SoftwareName]),
    s1 = Text.Select(s, {"A".."Z","a".."z"," "}),
    s2 = Text.Trim(Text.Combine(List.Select(Text.Split(s1, " "), each _ <> ""), " "))
in
    s2

and use it in your visuals.

Or if you are using DAX, you need to add a CC that takes everything before the first digit:

Software Base =
VAR s  = 'Software'[SoftwareName]
VAR L  = LEN(s) + 1
VAR pos =
    MINX(
        {
            FIND("0", s, 1, L),
            FIND("1", s, 1, L),
            FIND("2", s, 1, L),
            FIND("3", s, 1, L),
            FIND("4", s, 1, L),
            FIND("5", s, 1, L),
            FIND("6", s, 1, L),
            FIND("7", s, 1, L),
            FIND("8", s, 1, L),
            FIND("9", s, 1, L)
        },
        [Value]
    )
RETURN TRIM( IF(pos = L, s, LEFT(s, pos - 1)) )

Then you create a measure like:

Total Installs = COUNTROWS('Software')
-- or
Unique Devices = DISTINCTCOUNT('Software'[DeviceID])

[audreygerred · Accepted solution]

Hi! I suggest having your software name as two fields in your dim table, one field for Base name and one for the year, then you can do a distinct count on the name.