cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Merge Office Counts rows into single count

@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
Highlighted
Microsoft
Microsoft

Re: Merge Office Counts rows into single count

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

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

Highlighted
Microsoft
Microsoft

Re: Merge Office Counts rows into single count

@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
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors