cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
asoleiman85 Frequent Visitor
Frequent Visitor

Merge Office Counts rows into single count

Hello - 

 

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

 

Office.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Merge Office Counts rows into single count

@asoleiman85,


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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Moderator v-yuezhe-msft
Moderator

Re: Merge Office Counts rows into single count

@asoleiman85,

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])

1.PNG2.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
asoleiman85 Frequent Visitor
Frequent Visitor

Re: Merge Office Counts rows into single count

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])

Moderator v-yuezhe-msft
Moderator

Re: Merge Office Counts rows into single count

@asoleiman85,


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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.