Merge Office Counts rows into single count

[Anonymous]

Hello - 

I am trying to combine multiple version counts of office into a single row. 

Basically i want the Piechart object to only show a count of Office 365 installations but the query is pulling back the language extension on the end of the display name. 

Is there a way to merge rows that start with "Microsoft Office 365%" or modify the query to count matches?

Here is my query:

SELECT DISTINCT
SYS.Name0
,ARP.DisplayName0 As 'Software Name'
,ARP.Version0 As 'Version'
FROM
dbo.v_R_System As SYS
INNER JOIN dbo.v_FullCollectionMembership FCM On FCM.ResourceID = SYS.ResourceID
INNER JOIN dbo.v_Add_REMOVE_PROGRAMS As ARP On SYS.ResourceID = ARP.ResourceID
WHERE
(ARP.DisplayName0 LIKE 'Microsoft Office 365%'
OR ARP.DisplayName0 LIKE 'Microsoft Office Professional%'
OR ARP.DisplayName0 LIKE 'Microsoft Office Standard %')
ORDER BY Name0 ASC

Thanks

[v-yuezhe-msft]

@Anonymous,

You can create a new column in your table below, then involve the column in your visual or calculation.

New name = IF(LEFT(Table1[Display name],20)="Microsoft Office 365","Microsoft Office 365 installation",Table1[Display name])

Regards,
Lydia

[Anonymous]

Thank you!! That worked - 

Is there a way to continue the string to handle multiple variables? 

ex. 

New name = IF(LEFT(Query2[Software Name],20)="Microsoft Office 365","Microsoft Office 365",Query2[Software Name])

and

(LEFT(Query2[Software Name],39)="Microsoft Office Professional Plus 2013","Microsoft Office 2013",Query2[Software Name])

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please use DAX below.

New name = IF(LEFT(Query2[Software Name],20)="Microsoft Office 365","Microsoft Office 365",IF(LEFT(Query2[Software Name],39)="Microsoft Office Professional Plus 2013","Microsoft Office 2013",Query2[Software Name]))

Regards,
Lydia