cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

@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

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.

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Microsoft
Microsoft

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

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.
Anonymous
Not applicable

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

@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

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors